<?php
// +----------------------------------------------------------------------
// | Copyright © Sogu Technologies Co. Ltd 2018-2050 All rights reserved.
// +----------------------------------------------------------------------
// | File name: RiskCommand.php
// +----------------------------------------------------------------------
// | Author: liuju
// +----------------------------------------------------------------------
// | Description: 发邮件
// +----------------------------------------------------------------------
// | Others:
// * mail:send loan 发送前一天放款邮件
// +----------------------------------------------------------------------
// | History: 修改历史记录列表，每条修改记录应包含修改日期、修改者及修改内容简述
// +----------------------------------------------------------------------
// | 1.Date: 2019/3/13 Author: liuju Modification: 创建
// +----------------------------------------------------------------------
namespace App\Console\Commands;

use App\Library\Tools\ExcelExport;
use App\Mail\LoanMail;
use App\Mail\OverdueCommonMail;
use App\Mail\OverdueMoneyMail;
use App\Mail\OverdueMoreMail;
use App\Mail\RepayMoneyMail;
use App\Mail\WaitLoanMail;
use App\Mail\normaldueMoreMail;
use App\Mail\RemainMail;
use App\Mail\RepayMonitorMail;
use App\Models\LoanApply;
use App\Models\RepayRecord;
use App\Models\RepayRecordCommon;
use App\Models\RepayRecordMoney;
use App\Models\RepayRecordMonth;
use App\Models\RepaySummary;
use App\Models\SettingChannel;
use App\Models\SettingRepayRate;
use App\Models\User;
use App\Models\UserLoanRequest;
use Carbon\Carbon;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Mail;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use App\Mail\RepayMail;
use App\Facades\CommonPlatformRequestFacade;


class MailSend extends Command
{

    /**
     * 命令白名单
     */
    const ACTION_LIST = [
        'waitLoan', // 授信通过待放款
        'loan', //获取前一天放款数据
        'overdueOne',//获取逾期一天数据
        'overdueMore', //获取逾期6个月数据
        'repaymentcommon',//获取前一天平台还款数据
        'repaymentmoney',//获取前一天资方还款数据
        'repaymentcommonoflongxin',//获取前一天龙信平台还款数据
        'normalExce',//获取月底的应还款人数和金额
        'huankuanT',//获取每天的预计还款数据
        'huankuanT2',//获取两天后的预计还款数据
        'repaymentMonitor',//还款数据监控
        'commontable',//获取前一天平台还款数据更新版
        'moneytable',//获取前一天资方还款数据更新版
    ];


    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'mail:send {action=loan|overdueOne|overdueMore|normalExce|repaymentmoney|repaymentcommonoflongxin|huankuanT|huankuanT2|repaymentMonitor|commontable|moneytable} {--time=}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'send mail for task';

    /**
     * command action
     * @var
     */
    protected $action;

    /**
     * 对账要发的邮箱
     * @var string
     */
    private $_checkBill_1 = ['zhoubingbing@weishangtech.com', 'liusong@weishangtech.com']; //未要款表
    private $_checkBill_2 = 'cw@vjwealth.com'; //借款表

    private $repaymentMail = 'cw@vjwealth.com';//还款邮箱

    private $channel = ['LCC201709190002','LCC201709190006','LCC201709190007','LCC201709190009','LCC201709190008','LCC201709190011'];

    private  $remain_mail = ['16602123280@163.com','xuling@weishangtech.com','yanjun@weishangtech.com','wangxiaodong@weishangtech.com','sunchuanshi@vjwealth.com','xiayun@vjwealth.com'];//当天预计还款
    private $overdueMail = ['16602123280@163.com','sunchuanshi@vjwealth.com'];//贷款余额
    private  $repay_Monitor = ['linyf@sogukj.com','zhus@sogukj.com','gongxs@sogukj.com','changsh@sogukj.com','lijf@sogukj.com','liuqingyu@vjwealth.com'];
    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $this->initAction();
        $this->runAction();
    }


    /**
     * Description:初始化命令，检测是否操作合法
     * Author: liuju
     * DateTime: 2019-03-13 18:14
     */
    protected function initAction()
    {
        $this->action = $this->argument('action');
        if (!in_array($this->action, self::ACTION_LIST)) {
            $this->error('your command action `' . $this->action . '` is illegal');
            die;
        }
    }

    /**
     * Description:执行具体操作
     * Author: liuju
     * DateTime: 2019-03-13 18:14
     */
    protected function runAction()
    {
        $this->{$this->action}();
    }


    public function waitLoan()
    {
        ini_set('memory_limit', '1024M');
        set_time_limit(60);
        $data = DB::select("
        select 
 date_format(r.created_at,'%Y%m%d') as  sum_date 
 ,(select name from user_cert_ocrs as c where c.user_id=u.id and is_valid =1 limit 1) as name
 ,u.phone_num 
 ,'' as sex
,(select num  from user_cert_ocrs as c where c.user_id=u.id and is_valid =1 limit 1) as cert_num
 ,r.trans_id 
 ,date_format(r.apply_time,'%Y/%m/%d %H:%s') as apply_time
 ,r.loan_money/100 as apply_money
 ,r.actually_period 
 ,concat('快闪卡贷','-',u.source) as source
 ,concat('快闪卡贷','-',u.source) as source1
 ,date_format(r.credit_time,'%Y/%m/%d %H:%s') as  credit_time
 ,r.actually_loan_money/100 as  money_num
 , '未要款' as status
 ,(case r.channel when 'JCFC' then '晋商' when '10009' then '光大' else '其他' end ) as  channel_name
 from  user_loan_requests as r
 left join users as u on u.id = r.user_id where not EXISTS (select * from loan_apply as apply where apply.loan_request_id=r.id )
 and  r.status =3
 and r.credit_time >= '" . date('Y-m-d', strtotime('-7 day')) . "'
 order by r.id desc
        ");
        if (empty($data)) {
            die;
        }
        $headers = ['统计日期', '姓名', '手机号', '性别', '证件号', '申请编号', '申请时间', '申请金额', '期数', '注册渠道', '申请渠道', '审批通过时间', '审批金额', '要款状态', '资方标识'];
        $fields = ['sum_date', 'name', 'phone_num', 'sex', 'cert_num', 'trans_id', 'apply_time', 'apply_money', 'actually_period', 'source', 'source1', 'credit_time', 'money_num', 'status', 'channel_name'];

        $spreadSheet = new Spreadsheet();
        $sheet = $spreadSheet->getActiveSheet();
        //设置header
        $len = count($headers);
        for ($i = 0; $i < $len; $i++) {
            $sheet->setCellValue(chr(65 + $i) . "1", $headers[$i]);
        }

        //设置数值
        foreach ($data as $index => $item) {
            $row = $index + 2;
            $item = (array)$item;
            for ($i = 0; $i < $len; $i++) {
                $type = DataType::TYPE_STRING;
                if (in_array($i, [7, 8, 12])) {
                    $type = DataType::TYPE_NUMERIC;
                }
                $sheet->setCellValueExplicit(chr(65 + $i) . $row, $item[$fields[$i]], $type);
            }
        }
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadSheet);
        $file = 'wait_loan' . date('Ymd') . '.xlsx';
        $filePath = storage_path($file);
        $writer->save($filePath);
        Mail::to($this->_checkBill_1)->send(new WaitLoanMail($filePath));
        $this->info($file . ' export success!!');
        unlink($filePath);
    }

    /**
     * Description:获取前一天放款结果
     * Author: liuju
     * DateTime: 2019-03-13 18:14
     */
    public function loan()
    {
        ini_set('memory_limit', '2048M');
        set_time_limit(0);
        try{
            $date = Carbon::yesterday()->format('Y-m-d');
            $query = LoanApply::from('loan_apply as apply')
                ->select([
                    's.id as repay_id',
                    'u.id as user_id',                                                            //用户id
                    'request.credit_id',                                                          //授信id
                    'u.nfsp_user_id',                                                            //资方用户id
                    'u.source',
                    'u.phone_num',                                                               //用户电话
                    'apply.out_trade_id',                                                        //借款订单号
                    'request.channel',                                                           // 客户渠道
                    'request.trans_id',
                    'apply.loan_id',                                                             // 放款交易号
                    'apply.amount',                                                             // 放款金额
                    'apply.period',                                                             // 分期数
//                    'apply.pre_insurance_amt',
                    'apply.performance_insurance',
                    'apply_time',         // 放款申请时间
                    'loan_time',      // 实际放款时间
                    DB::raw('(select count(p1.id)  from  loan_apply as p1 where p1.user_id=apply.user_id and p1.loan_status in (2,6)) as apply_num'),
                ])
                ->with(['getUser.validCert', 'getSummary.recordMany', 'getSummary.money'])
                ->leftJoin('user_loan_requests as request', 'request.id', '=', 'apply.loan_request_id')
                ->leftJoin('users as u', 'u.id', '=', 'apply.user_id')
                ->leftJoin('repay_summaries' . ' as s', 's.loan_id', '=', 'apply.loan_id')
                ->whereIn('apply.loan_status', [LoanApply::LOAN_STATUS_SUCCESS, LoanApply::LOAN_HANDLE_CHANGE_CARD_SUCCESS])
                ->whereDate('apply.loan_time', Carbon::yesterday())
            ;
            $data = [];
            foreach ($query->cursor() as $item) {
                $item->channel_name = $item->channel == 'JCFC' ? '晋商' : '光大';
                $item->apply_time = date('Y/m/d', strtotime($item->apply_time));
                $item->loan_time = date('Y/m/d', strtotime($item->loan_time));
                $item->channle_num = '';
                $item->channel_source = '';
                $item->product_name = '卡贷2';
                $rate = SettingRepayRate::where('period',$item->period)->where('channel_code',$item->channel)->first();
                $rate = $rate->insurance_rate;
                $item->pre_insurance_amt = bcmul($item->amount,$rate);
                unset($rate);

                $item->platform_service = RepaySummary::getOrderTotalMoney($item->getSummary, 0, 'manage');
                $item->platform_other = RepaySummary::getOrderTotalMoney($item->getSummary, 0, 'month');
                $item->name = $item->getUser->valid->name;
                $item->cert_num = $item->getUser->valid->num;
                $period = 1;
                $amtTotal = 0;
                $amountTotal = 0;

                foreach ($item->getSummary->singleMoney as $key => $item1) {

                    $propertyAmount = 'period_' . $period . '_amount';
                    $propertyDay = 'period_' . $period . '_day';
                    $propertyAmt = 'period_' . $period . '_amt';
                    $item->$propertyAmt = $item1->lending_money;
                    $propertyService = 'period_' . $period . '_service';
                    $propertyOther = 'period_' . $period . '_other';
                    $item->$propertyService = '';
                    $item->$propertyOther = '';
                    foreach ($item->getSummary->recordMany as $item2) {
                        if ($item2->id == $item1->record_id) {
                            $item->$propertyAmount = $item2->amount;
                            $item->$propertyDay = Carbon::parse($item2->normal_date)->format('Y/m/d');
                            $item->$propertyService = RepaySummary::getOrderTotalMoney($item->getSummary, $period, 'manage');
                            break;
                        }
                    }
                    $amtTotal = bcadd($item->$propertyAmt, $amtTotal, 2);
                    $amountTotal = bcadd($item->$propertyAmount, $amountTotal, 2);
                    $propertyTotal = 'period_' . $period . '_total';
                    $item->$propertyTotal = bcadd($item->$propertyAmt, $item->$propertyAmount, 2);

                    $period++;
                }
                if ($period < 12) {
                    while ($period <= 12) {
                        $propertyAmount = 'period_' . $period . '_amount';
                        $propertyDay = 'period_' . $period . '_day';
                        $propertyAmt = 'period_' . $period . '_amt';
                        $propertyTotal = 'period_' . $period . '_total';
                        $propertyService = 'period_' . $period . '_service';
                        $propertyOther = 'period_' . $period . '_other';
                        $item->$propertyTotal = '0.00';
                        $item->$propertyAmt = "0.00";
                        $item->$propertyAmount = "0.00";
                        $item->$propertyDay = '';
                        $item->$propertyService = '';
                        $item->$propertyOther = '';
                        $period++;
                    }
                }

                $item->amount_total = $item->amount;
                $item->amt_total = $amtTotal;
                $item->all_total = RepaySummary::getOrderTotalMoney($item->getSummary);
                unset($item->getUser, $item->getSummary);
                $data[] = $item->toArray();
            }

            if (!$data) {
                throw new \Exception(Carbon::yesterday()->format('Y-m-d').'无数据');
            }
            $header = [
                '授信ID',
                '产品',
                '新流用户号',
                '姓名',
                '手机号',
                '身份证',
                '客户渠道号',
                '客户渠道来源',
                '借款订单号',
                '放款交易号',
                '放款资方渠道',
                '放款金额',
                '分期',
                '申请放款时间',
                '笔数',
                '实际放款时间',
                '应收总金额',
                '应收总本金',
                '应收总利息',
                '贷后管理费',
                '应收平台其他费用',
                '应收前置保费',
                '应收信用保证险'
            ];
            $fields = [
                'credit_id',
                'product_name',
                'nfsp_user_id',
                'name',
                'phone_num',
                'cert_num',
                'source',
                'channel_source',
                'trans_id',
                'loan_id',
                'channel_name',
                'amount',
                'period',
                'apply_time',
                'apply_num',
                'loan_time',
                'all_total',
                'amount_total',
                'amt_total',
                'platform_service',
                'platform_other',
                'pre_insurance_amt',
                'performance_insurance',
            ];
            $i = 1;
            while ($i <= 12) {
                $header[] = '还款日期-' . $i . '期';
                $fields[] = 'period_' . $i . '_day';
                $header[] = '还款总金额-' . $i . '期';
                $fields[] = 'period_' . $i . '_total';
                $header[] = '还款本金-' . $i . '期';
                $fields[] = 'period_' . $i . '_amount';
                $header[] = '还款利息-' . $i . '期';
                $fields[] = 'period_' . $i . '_amt';
                $header[] = '贷后管理费-' . $i . '期';
                $fields[] = 'period_' . $i . '_service';
                $header[] = '还款其他费用-' . $i . '期';
                $fields[] = 'period_' . $i . '_other';
                $i++;
            }

            $file = 'loan' . $date . '.xlsx';
            $path = storage_path($file);
            $excel = new ExcelExport($data,$header,$fields,$file,$path);
            $excel->commandExport($path);
            Mail::to($this->_checkBill_2)->send(new LoanMail($path));
            $this->info('export loan data is ok!!!');
            unlink($path);
        }catch (\Exception $e){
            $this->info('借款表异常推送：'.$e->getMessage());
        }
    }


    /**
     * Description:逾期表(所有)
     * Author:Mmy
     */
    public function overdueMore()
    {
        $to_mail = ['wangxiaodong@weishangtech.com','yanjun@weishangtech.com','sunchuanshi@vjwealth.com','xiayun@vjwealth.com','zhangjiyan@vjwealth.com','jiahuilin@vjwealth.com','zouwenchao@nflow.cn','songyi@weishangtech.com','dingmayun@vjwealth.com'];
        $this->getOverdueMoreData($to_mail);
    }
    /*
     * 重新导出逾期数据
     */
    public function test($to_mail)
    {
        $t0 = microtime(true);
        ini_set('max_execution_time', 0);
        ini_set('memory_limit', '2048M');
        //查找当前逾期的没有还的记录
        $current_date = Carbon::now()->format('Y-m-d');
        //查询有逾期的订单id
        RepayRecord::where(function ($query) {
            $query->where('status', RepayRecord::STATUS_ONE);
            $query->orWhere('status', RepayRecord::STATUS_THREE);
        })
            ->where('normal_date', '<', $current_date)
            ->groupBy('summary_id')
            ->chunk(2000, function (Collection $summary_collection) use ($current_date, $t0) {
                $summary_ids = $summary_collection->pluck('summary_id');
                $summaries = RepaySummary::leftJoin('repay_records as a', 'repay_summaries.id', '=', 'a.summary_id')
                    ->leftJoin('repay_record_months as b', 'a.id', '=', 'b.record_id')
                    ->leftJoin('repay_record_moneys as c', 'a.id', '=', 'c.record_id')
                    ->leftJoin('repay_record_commons as d', 'a.id', '=', 'd.record_id')
                    ->leftJoin('user_loan_requests as e', 'e.user_id', '=', 'repay_summaries.uid')
                    ->whereIn('repay_summaries.id', $summary_ids)
                    ->where('a.period', '>', 0)
                    ->where('e.current',1)
                    ->select([
                        'repay_summaries.id as summary_id',
                        'repay_summaries.loan_id as case_code',
                        'repay_summaries.uid',
                        'repay_summaries.period',
                        'repay_summaries.amount',
                        'repay_summaries.rate',
                        'repay_summaries.data_type',
                        'repay_summaries.trans_id',
                        'repay_summaries.loan_id',
                        //a 是repay_records
                        'a.id as record_id',
                        'a.amount as record_amount',
                        'a.period as record_period',
                        'a.status as record_status',
                        'a.normal_date',
                        'a.pay_off as pay_off',
                        /*
                         * zlk新加字段
                         */
                        'a.repay_plan_number as repay_plan_number',
                        'a.created_at as record_create_at',
                        'a.repay_date as repay_date',
                        //b 是repay_record_months
                        'b.amount as month_amount',
                        //c 是repay_record_moneys
                        'c.fines_money as money_fines',
                        'c.lending_money as money_lending',
                        'c.manage_money as money_manage',
                        /*
                         * zlk新加字段
                         */
                        'c.relief_amount as relief_amount',
                        //d 是repay_record_commons
                        'd.fines_money as common_fines',
                        'd.lending_money as common_lending', //兼容老数据
                        'd.manage_money as common_manage',
                        //e是user_loan_requests
                        'e.credit_id as credit_id'
                    ])
                    ->orderBy('a.period', 'asc')
                    ->get();
                $summaries_exchange = ArrayTools::valuesToKey($summaries->toArray(), 'summary_id');
                //单个还款订单为单位
                foreach ($summaries_exchange as $key => $records) {
                    if (empty($records))
                        continue;
                    /**
                     * 案件信息
                     */
                    // 结款号
                    $case_msg['caseCode'] = $records[0]['case_code'];
                    $temp_period_overdue = []; //当前逾期期数
                    $temp_success_period = 0; //已还期数
                    $temp_overdue_days = 0; //逾期天数
                    $temp_fines = 0; //未还罚息
                    $temp_amount = 0; //未还剩余本金
                    $temp_lending = 0; //未还剩余利息
                    $temp_repayment_money_current = 0; //当前已还总额
                    $temp_not_repayment_money_current = 0; //当前未还总额
                    $temp_not_normal_money = 0; //当前未到还款日总额
                    $temp_overdue_time = ''; //最早逾期的起始日
                    $relief_amount = ''; //减免金额
                    $repayPlanList = []; //还款计划表
                    //循环处理订单记录
                    array_walk($records, function ($item) use (
                        &$temp_period_overdue,
                        &$temp_success_period,
                        &$temp_overdue_days,
                        &$temp_fines,
                        &$temp_amount,
                        &$temp_lending,
                        &$temp_repayment_money_current,
                        &$temp_not_repayment_money_current,
                        &$temp_not_normal_money,
                        &$temp_overdue_time,
                        &$relief_amount,
                        &$repayPlanList,
                        $current_date
                    ) {

                        $moneyData = [
                            'all_money' => 0,
                            'ys_amount' => 0,
                            'lending_money' => 0,
                            'money_fines_money' => 0,
                            'amount_lending_money' => 0,
                            'common_fines_money' => 0,
                            'manage_money' => 0,
                            'month_money' => 0,
                            'yh_all_money' => 0,
                            'yh_amount' => 0,
                            'yh_lending_money' => 0,
                            'yh_money_fines_money' => 0,
                            'yh_common_fines_money' => 0,
                            'yh_manage_money' => 0,
                            'yh_month_money' => 0,
                            'yh_other_money' => 0,
                            'remain_amount' => 0
                        ];
                        $moneyData['record_id'] = $item['record_id'];
                        $moneyData['credit_id'] = $item['credit_id']; //授信id
                        $moneyData['product'] = $this->repayType($summary->repay_type);//产品
                        $moneyData['user_id'] = $summary->users->nfsp_user_id;//用户号
                        $moneyData['name'] = isset($summary->users->valid) ? $summary->users->valid->name : '';//用户姓名1
                        $moneyData['phone_num'] = $summary->users->phone_num;//手机号
                        $moneyData['num'] = isset($summary->users->valid) ? $summary->users->valid->num : '';//身份证号2
                        $moneyData['source_code'] = $summary->users->source;//渠道号
                        $moneyData['source_name'] = '';//渠道来源
                        $moneyData['out_trade_id'] = $summary->relationLoanApply->out_trade_id;//放款交易号
                        $channel_name = $this->getChannelName($summary->loan_request_id, $all_channel);
                        $moneyData['channel_name'] = $channel_name;//放款资方渠道
                        $moneyData['amount'] = $summary->amount;//放款金额
                        $moneyData['period_count'] = $summary->period_count;//分期期数
                        $moneyData['status'] = '逾期';//还款状态
                        $moneyData['period'] = $summary->period;//当前期数
                        $moneyData['normal_date'] = $record->normal_date;//预计还款日期
                        $moneyData['failure'] = $record->money->failure;//失败原因
                        $moneyData['current_period'] = $record->period;//当前逾期是第几期
                        $moneyData['overdue_day'] = count_days(Carbon::now()->toDateString(), $record->normal_date);//逾期天数
                        $moneyData['repay_type'] = '';//还款类型
                        $moneyData['current_date'] = Carbon::now()->toDateString();//对账日期
                        $remainMoney = $this->getRemainMoney($summary->id);//剩余未还
                        $moneyData['remain_amount'] = $remainMoney['remainAmount'];//剩余未还本金
                        $isNew = RepaySummary::getLoanChannel($summary->id);
                        $recordMany = $summary->recordMany;






                        if ($item['record_status'] == RepayRecord::STATUS_ONE
                            || $item['record_status'] == RepayRecord::STATUS_THREE
                        ) {
                            //待处理 || 失败的记录
                            //当前期的未还金额
                            $current_not_repayment_money = RepayRecord::getNotRepaymentMoneyCurrent($item);
                            $temp_not_repayment_money_current += $current_not_repayment_money;
                        }
                        if (($item['record_status'] == RepayRecord::STATUS_ONE
                                || $item['record_status'] == RepayRecord::STATUS_THREE)
                            && $item['normal_date'] > $current_date
                        ) {
                            //待处理 || 失败的记录 && 未到还款日(包含还款日)
                            $current_not_repayment_money = RepayRecord::getNotRepaymentMoneyCurrent($item);
                            $temp_not_normal_money += $current_not_repayment_money;
                        }
                        if (($item['record_status'] == RepayRecord::STATUS_ONE
                                || $item['record_status'] == RepayRecord::STATUS_THREE)
                            && $item['normal_date'] < $current_date
                        ) {
                            //待处理 || 失败的记录 && 逾期
                            $temp_period_overdue[] = $item['record_period'];
                            //单个最长逾期天数
                            if (0 == $temp_overdue_days)
                                $temp_overdue_days = Carbon::parse($item['normal_date'])->diffInDays($current_date, false);
                            //计算未还罚息
                            $temp_fines += bcadd($item['money_fines'], $item['common_fines'], 2);
                            //未还本金
                            $temp_amount += $item['record_amount'];
                            //未还利息和 = 提前结清手续费
                            $temp_lending += bcadd($item['money_lending'], $item['common_lending'], 2);
                            if (empty($temp_overdue_time))
                                $temp_overdue_time = Carbon::parse($item['normal_date'])->addDays(1)->timestamp;
                        }
                        if ($item['record_status'] == RepayRecord::STATUS_FOUR) {
                            //成功的记录
                            ++$temp_success_period;
                            //当前期已还总额
                            $current_repayment_money = RepayRecord::getRepaymentMoneyCurrent($item);
                            $temp_repayment_money_current += $current_repayment_money;
                        }
                        //todo 还款计划表优化
                        $result = RepayRecord::getRepaymentPlan($item);
//                            $result['debtMoney']=bcsub($temp_not_repayment_money_current, $temp_not_normal_money, 2);
//                            $repayPlanList[] = $result;
                    });
                    //当前逾期期数
                    $case_msg['periodOverdue'] = implode(',', $temp_period_overdue);
                    //已还期数
                    $case_msg['periodRepay'] = $temp_success_period;
                    //逾期天数（单个最长逾期时间）
                    $case_msg['overdueDay'] = $temp_overdue_days;
                    //未还罚息
                    $punish_interest_total = bcdiv($temp_fines, 100, 2);
                    $case_msg['punishInterestTotal'] = $punish_interest_total;
                    //未还剩余本金
                    $residual_money = bcdiv($temp_amount, 100, 2);
                    $case_msg['residualMoney'] = $residual_money;
                    //提前还清手续费 = 未还利息和
                    $repayment_money = bcdiv($temp_lending, 100, 2);
                    // 结清应还（本金+手续费+罚息）
                    $case_msg['settleMoney'] = bcadd(bcadd($punish_interest_total, $residual_money, 2), $repayment_money, 2);
                    // 当前已还总额
                    $case_msg['repaymentMoneyCurrent'] = $temp_repayment_money_current;
                    // 逾期总额
                    $case_msg['debtMoney'] = bcsub($temp_not_repayment_money_current, $temp_not_normal_money, 2);
                    // 逾期起始日
                    Log::debug('collection_update_command', ['case_msg'=>$case_msg,'result'=>$repayPlanList]);
                    $case_msg['overdueTime'] = empty($temp_overdue_time) ? '' : $temp_overdue_time * 1000;//转成时间戳（毫秒）
                    $result = CollectionRequestFacade::sendUpdateCase($case_msg, $repayPlanList);
                    Log::debug('collection_update_command', ['result'=>$result]);

                    $this->info('批处理时间:' . round(microtime(true) - $t0, 2) . 's');

            }
            });
    }
    public function getOverDueMoreData($to_mail)
    {
        ini_set('max_execution_time', 0);
        ini_set('memory_limit', '2048M');
        //查找当前逾期的没有还的记录
        $today = date('Y-m-d');
        $summary_ids = RepayRecord::distinct()
            ->where('pay_off', RepayRecord::PAY_OFF_ONE)
            ->where('normal_date', '<', $today)
            ->where('repay_records.status', '<>', RepayRecord::STATUS_FOUR)
            ->pluck('repay_records.summary_id')
            ->toArray();
        $count_num=count($summary_ids);
        $repaySummaryInfo = RepaySummary::with([
            'recordMany' => function ($q) use ($today) {
                $q->where('status', '<>', RepayRecord::STATUS_FOUR)
                    ->where('pay_off', RepayRecord::PAY_OFF_ONE)
                    ->whereDate('normal_date', '<', $today)
                    ->orderBy('period')
                    ->select('id', 'uid', 'summary_id', 'period', 'amount', 'status', 'pay_off', 'normal_date');
            },
            'recordMany.money',
            'recordMany.common',
            'recordMany.month',
            'relationLoanApply' => function ($q) {
                $q->whereIn('loan_status', [LoanApply::LOAN_STATUS_SUCCESS, LoanApply::LOAN_HANDLE_CHANGE_CARD_SUCCESS])
                    ->select('out_trade_id', 'loan_request_id', 'loan_time', 'created_at');
            },
            'relationUserLoanRequests:id,credit_id',
            'users:id,phone_num,nfsp_user_id,source'])
            ->where('repay_summaries.status', RepaySummary::STATUS_ONE)
            ->where('repay_summaries.current_repay', RepaySummary::CURRENT_REPAY_ONE)
            ->whereIn('id', $summary_ids);
        $excelData = array();
        $all_channel = SettingChannel::pluck('channel_name', 'channel_code')->toArray();
        $repaySummaryInfo->chunk(2000, function ($repaySummaryInfo) use ($all_channel, &$excelData) {
            foreach ($repaySummaryInfo as $k => $summary) {
                $moneyData = ['all_money' => 0, 'ys_amount' => 0, 'lending_money' => 0, 'money_fines_money' => 0, 'amount_lending_money' => 0, 'common_fines_money' => 0, 'manage_money' => 0, 'month_money' => 0, 'yh_all_money' => 0, 'yh_amount' => 0, 'yh_lending_money' => 0, 'yh_money_fines_money' => 0, 'yh_common_fines_money' => 0, 'yh_manage_money' => 0, 'yh_month_money' => 0, 'yh_other_money' => 0, 'remain_amount' => 0];
                $record = $summary->recordMany->first();
                if (!$record) {
                    continue;
                }
                $moneyData['record_id'] = $record->id;
                $moneyData['credit_id'] = isset($summary->relationUserLoanRequests) ? $summary->relationUserLoanRequests->credit_id : '';
                $moneyData['product'] = $this->repayType($summary->repay_type);//产品
                $moneyData['user_id'] = $summary->users->nfsp_user_id;//用户号
                $moneyData['name'] = isset($summary->users->valid) ? $summary->users->valid->name : '';//用户姓名1
                $moneyData['phone_num'] = $summary->users->phone_num;//手机号
                $moneyData['num'] = isset($summary->users->valid) ? $summary->users->valid->num : '';//身份证号2
                $moneyData['source_code'] = $summary->users->source;//渠道号
                $moneyData['source_name'] = '';//渠道来源
                $moneyData['out_trade_id'] = $summary->relationLoanApply->out_trade_id;//放款交易号
                $channel_name = $this->getChannelName($summary->loan_request_id, $all_channel);
                $moneyData['channel_name'] = $channel_name;//放款资方渠道
                $moneyData['amount'] = $summary->amount;//放款金额
                $moneyData['period_count'] = $summary->period_count;//分期期数
                $moneyData['status'] = '逾期';//还款状态
                $moneyData['period'] = $summary->period;//当前期数
                $moneyData['normal_date'] = $record->normal_date;//预计还款日期
                $moneyData['failure'] = $record->money->failure;//失败原因
                $moneyData['current_period'] = $record->period;//当前逾期是第几期
                $moneyData['overdue_day'] = count_days(Carbon::now()->toDateString(), $record->normal_date);//逾期天数
                $moneyData['repay_type'] = '';//还款类型
                $moneyData['current_date'] = Carbon::now()->toDateString();//对账日期
                $remainMoney = $this->getRemainMoney($summary->id);//剩余未还
                $moneyData['remain_amount'] = $remainMoney['remainAmount'];//剩余未还本金
                $isNew = RepaySummary::getLoanChannel($summary->id);
                $recordMany = $summary->recordMany;
                foreach ($recordMany as $key => $value) {
                    
                    $moneyInfo = RepaySummary::getRepayMoney($value);
                    $moneyData['all_money'] += $moneyInfo['all_money'];//应收总金额
                    $moneyData['ys_amount'] += $moneyInfo['amount'];//应收本金
                    $moneyData['lending_money'] += $moneyInfo['lending_money'];//应收利息
                    $moneyData['money_fines_money'] += $moneyInfo['money_fines_money'];//资方罚息
//                    $moneyData['amount_lending_money'] += $moneyInfo['all_money'];//本息和
                    $moneyData['common_fines_money'] += $moneyInfo['common_fines_money'];//平台罚息
                    $moneyData['manage_money'] += $moneyInfo['manage_money'];//应收贷后管理费
                    $moneyData['month_money'] += $moneyInfo['month_money'];//应收月保费
                    if ($isNew) {
                        $yihuanMoney = RepaySummary::getYihuanMoney($value);
                    } else {
                        $yihuanMoney = RepaySummary::getYihuanMoneyForOldData($value);
                    }
                    $yh_all_money = $yihuanMoney['all_money'] + $yihuanMoney['money_repayment_money'] + $yihuanMoney['common_repayment_money'];
                    $moneyData['yh_all_money'] += !empty($yh_all_money) ? $yh_all_money : 0;//已还总金额
                    $moneyData['yh_amount'] += $yihuanMoney['amount'];//已还本金
                    $moneyData['yh_lending_money'] += $yihuanMoney['lending_money'];//已还利息
                    $moneyData['yh_money_fines_money'] += $yihuanMoney['money_fines_money'];//已还资方罚息
                    $moneyData['yh_common_fines_money'] += $yihuanMoney['common_fines_money'];//已还平台罚息
                    $moneyData['yh_manage_money'] += $yihuanMoney['manage_money'];//已还贷后管理费
                    $moneyData['yh_month_money'] += $yihuanMoney['month_money'];//已还月保费
                    $moneyData['yh_other_money'] += bcsub($yh_all_money, $moneyInfo['all_money'], 2);//已还其他费用(溢缴)
                }
                //新增字段
                $moneyData['loan_time'] = date('Y-m-d', strtotime($summary->relationLoanApply->loan_time));//放款日期
                $moneyData['sign_date'] = date('Y-m-d', strtotime($summary->relationLoanApply->created_at));//签约日期
                $moneyData['paid_period'] = $summary->period - 1;//已还期数
                $period_count = $this->getPeriod($summary->id);
                $moneyData['overdue_period'] = $period_count['overdue_period'];//逾期还款期数
                $moneyData['normal_period'] = $period_count['normal_period'];//正常还款期数
                $moneyData['loan_remain'] = bcadd(bcadd($remainMoney['remainAmount'], $remainMoney['remainLending'], 2), $remainMoney['remainManage'], 2);//贷款余额(本金+利息+管理费)
                $moneyData['account_remain'] = 0;//账户余额
                $excelData[] = $moneyData;
            }
        });
        $this->exportData($excelData, $to_mail);
    }

    //获取已还期数
    protected function getPeriod($summary_id)
    {
        //正常还款
        $normal_period = RepayRecord::where('status', 4)->where('summary_id', $summary_id)->where(DB::raw('normal_date'),'>=', DB::raw('repay_date'))->count();
        //逾期还款
        $overdue_period = RepayRecord::where('status', 4)->where('summary_id', $summary_id)->where(DB::raw('normal_date'),'<', DB::raw('repay_date'))->count();

        return ['normal_period' => $normal_period, 'overdue_period' => $overdue_period];
    }

    //获取剩余未还总金额
    protected function getRemainMoney($summary_id)
    {
        $records = RepayRecord::with('money:lending_money,manage_money,record_id', 'common:lending_money,manage_money,record_id')->where('summary_id', $summary_id)->where('status', '<>', RepayRecord::STATUS_FOUR)->where('period','>',0)->select('id', 'amount')->get()->toArray();
        $remainAmount = array_sum(array_column($records, 'amount'));
        $remainMoneyLending = array_sum(array_column(array_column($records, 'money'), 'lending_money'));
        $remainCommonLending = array_sum(array_column(array_column($records, 'common'), 'lending_money'));
        $remainLending = bcadd($remainMoneyLending, $remainCommonLending, 2);
        $remainMoneyManage = array_sum(array_column(array_column($records, 'money'), 'manage_money'));
        $remainCommonManage = array_sum(array_column(array_column($records, 'common'), 'manage_money'));
        $remainManage = bcadd($remainMoneyManage, $remainCommonManage, 2);

        return ['remainAmount'=>$remainAmount, 'remainLending'=>$remainLending, 'remainManage'=>$remainManage];
    }

    // 获取产品类型
    protected function repayType($type)
    {
        $name = '';
        switch ($type) {
            case 0:
                $name = '快闪卡贷2';
                break;
            case 1:
                $name = '快闪卡贷';
                break;
            case 2:
                $name = '快闪卡贷1';
                break;
        }

        return $name;
    }

    //导出逾期数据
    protected function exportData($excelData, $to_mail)
    {
        $header = ['授信ID', '产品', '用户号', '用户姓名', '手机号', '身份证号', '渠道号', '渠道来源', '借款订单号', '放款资方渠道', '放款金额', '分期期数', '还款状态', '当前期数', '预计还款日期', '应收总金额', '应收本金', '应收利息', '资方罚息', '平台罚息', '应收贷后管理费', '应收月保费',
//            '本息和',
            '失败原因', '当前逾期期数', '当前逾期天数', '还款类型', '导表日期', '已还款总金额', '已还本金', '已还利息', '已还资方罚息', '已还平台罚息', '已还贷后管理费', '已还月保费', '已还其他费用(溢缴)', '剩余未还本金', '放款日期', '签约日期','已还期数', '逾期还款期数','正常还款期数','贷款余额', '账户余额'];
        $fields = ['credit_id', 'product', 'user_id', 'name', 'phone_num', 'num', 'source_code', 'source_name', 'out_trade_id', 'channel_name', 'amount', 'period_count', 'status', 'period', 'normal_date', 'all_money', 'ys_amount', 'lending_money', 'money_fines_money', 'common_fines_money', 'manage_money', 'month_money',
//            'amount_lending_money',
            'failure', 'current_period', 'overdue_day', 'repay_type', 'current_date',  'yh_all_money', 'yh_amount', 'yh_lending_money', 'yh_money_fines_money', 'yh_common_fines_money', 'yh_manage_money', 'yh_month_money', 'yh_other_money', 'remain_amount', 'loan_time', 'sign_date', 'paid_period', 'overdue_period', 'normal_period', 'loan_remain', 'account_remain'];
        $spread = new Spreadsheet();
        $sheet = $spread->getActiveSheet();
        //设置header
        $len = count($header);
        for ($i = 0; $i < $len; $i++) {
            if ($i <= 25) {
                $sheet->setCellValue(chr(65 + $i) . "1", $header[$i]);
            } else {
                $n = floor($i / 26);
                $sheet->setCellValue(chr(65 + $n - 1) . chr(65 + ($i % 26)) . "1", $header[$i]);
            }
        }
        $len = count($fields);
        $i_num = [4, 6, 10, 11, 13, 15, 16, 17, 18, 19, 20, 21, 23, 24, 27, 28, 29, 30, 31, 32, 33, 34, 35, 38, 39, 40,41];
        foreach ($excelData as $key => $item) {
            $row = 2 + $key;
            for ($i = 0; $i < $len; $i++) {
                if ($i <= 25) {
                    $position = chr(65 + $i) . $row;
                } else {
                    $n = floor($i / 26);
                    $position = chr(65 + $n - 1) . chr(65 + ($i % 26)) . $row;
                }
                $sheet->setCellValueExplicit($position, $item[$fields[$i]], DataType::TYPE_STRING);
                if (in_array($i, $i_num)) {
                    $sheet->setCellValueExplicit($position, $item[$fields[$i]], DataType::TYPE_NUMERIC);
                }
            }
        }
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spread);
        $file = 'overdue' . date('Ymd') . '.xlsx';
        $filePath = storage_path($file);
        $writer->save($filePath);
        Mail::to($to_mail)->send(new OverdueMoreMail($filePath));

        $this->info($file . ' export success!!');
        unlink($filePath);
    }

    /**
     * Description:获取放款资方渠道名称
     * Author: Mmy
     * DateTime: 2019-03-15 15:51
     * @param $loan_request_id
     * @param $all_channel
     * @return string
     */
    protected function getChannelName($loan_request_id, $all_channel)
    {
        $requestInfo = UserLoanRequest::select('channel')->find($loan_request_id);
        $channel_name = '';

        if (key_exists($requestInfo->channel, $all_channel)) {
            $channel_name = $all_channel[$requestInfo->channel];
        } else {
            if ($requestInfo->channel == 'long_xin') {
                $channel_name = '龙信';
            }
        }

        return $channel_name;
    }


    /**
     * Description:平台报表
     * Author:zhaiyukang
     */
    public function repaymentcommon(){
        //查询昨天的还款成功记录
        $time = $this->option('time');
        $repay_date = Carbon::yesterday()->toDateString();
        if(!empty($time)){
            $repay_date = $time;
        }
        $back = new RepayRecord();
        $back->setConnection('backend');
        $info =$back->query()
            ->from('repay_records as records')
            ->with(['month','common','relationSummary'])
            ->orWhere(function ($q) use($repay_date){
                $q->where('records.status',RepayRecord::STATUS_FOUR)->where('pay_off',RepayRecord::PAY_OFF_ONE)->whereDate('records.repay_date',$repay_date);
            })
            ->orWhere(function ($q) use($repay_date){
                $q->where('records.period',0)
                    ->where('records.status',RepayRecord::STATUS_FOUR)
                    ->where('records.pay_off',RepayRecord::PAY_OFF_TWO)
                    ->whereDate('records.repay_date',$repay_date);
            })
            ->get();
        $excelData = array();
        if(!empty($info)){
            foreach ($info as $k => $value){
                $is_channel = RepaySummary::getLoanChannel($value->summary_id);
                if($is_channel){
                    //晋商,新光大本金是不扣的
                    $total = $value->common->lending_money + $value->common->fines_money + $value->common->manage_money + $value->common->repayment_money;
                    if($total == 0){
                        continue;
                    }
                }
                $summaryInfo = $value->relationSummary;
                $loanApply = LoanApply::select('out_trade_id','loan_time')->where('loan_request_id',$summaryInfo->loan_request_id)->first();
                $userLoanRequest = UserLoanRequest::select('credit_id')->find($summaryInfo->loan_request_id);
                $arrayData = array();
                $arrayData['record_id'] = $value->id;
                $arrayData['loan_request_id'] = $userLoanRequest->credit_id;
                $arrayData['product'] = $this->getRepayName($summaryInfo->repay_type);//产品
                $userInfo = $this->getUserInfo($value->uid);
                $arrayData['user_id'] = $userInfo->nfsp_user_id;//用户号
                $arrayData['name'] = $userInfo->name;//用户姓名
                $arrayData['phone_num'] = $userInfo->phone_num;//手机号
                $arrayData['cert_number'] = !empty($userInfo->cert_no)?$userInfo->cert_no:$userInfo->cert_number;//身份证号
                $arrayData['loan_order_id'] = isset($loanApply->out_trade_id) ? $loanApply->out_trade_id : '';//借款订单号
                $arrayData['loan_time'] = isset($loanApply->loan_time) ? $loanApply->loan_time : '';//放款时间
                $arrayData['source_code'] = $userInfo->source;//渠道号
                $arrayData['source_name'] = '';//渠道来源
                $arrayData['loan_id'] = $summaryInfo->loan_id;//放款交易号
                $channel_name = $this->getChannelNames($summaryInfo);
                $arrayData['channel_name'] = $channel_name;//放款资方渠道
                $arrayData['amount'] = $summaryInfo->amount;//放款金额
                $arrayData['period_count'] = $summaryInfo->period_count;//分期期数
                $historyMoney = $this->getHistoryMoney($value,$summaryInfo);//获取历史各种金额
                $arrayData['history_all_money'] = $historyMoney['history_all_money'];//历史总金额
                $arrayData['history_amount'] = $historyMoney['history_amount'];//历史总本金
                $arrayData['history_lending_money'] = $historyMoney['history_lending_money'];//历史总利息
                $arrayData['history_manage_money'] = $historyMoney['history_manage_money'];//历史总贷后管理费
                $arrayData['history_money_fines_money'] = $historyMoney['history_money_fines_money'];//历史总资方罚息
                $arrayData['history_common_fines_money'] = $historyMoney['history_common_fines_money'];//历史总平台罚息
                $arrayData['history_other_money'] = $historyMoney['history_other_money'];//历史总其他金额
                $arrayData['trade_number'] = isset($value->common->pay_order_no) ? $value->common->pay_order_no : '';//还款交易号
                if($repay_date == '2019-03-17' || $repay_date == '2019-03-18'){
                    if(!empty($value->common->trade_no)){
                        $data = [
                            'channelId' => 'JD_WITHHOLD', //支付渠道 
                            'orderId' => $value->common->trade_no, //用户流水号 ;
                        ];
                        $res = CommonPlatformRequestFacade::getCommonRepayResult($data);
                        if($res['data']['status'] != 1){
                            continue;
                        }else{
                            $arrayData['trade_number'] = $res['data']['payOrderNo'];
                        }

                    }
                }
                if(empty($value->common->pay_order_no) && empty($value->common->third_record_id) && empty($value->common->trade_no)){
                    continue;
                }
                $channel = $is_channel == true ? RepayRecordCommon::NEW_COMMON_MER : RepayRecordCommon::OLD_COMMON_MER;
                if($value->repay_type == RepayRecord::REPAY_TYPE_ACCOUNT){
                    $arrayData['channel'] = '线下还款';
                    $arrayData['trade_number'] = '';
                }else{
                    $arrayData['channel'] =$channel == RepayRecordCommon::NEW_COMMON_MER ? '联仲代扣' : '蔚商代扣';
                }
                $arrayData['repay_type'] = $this->getRepayTypeold($value);
                $arrayData['normal_date'] = str_replace('-','/',$value->normal_date);
                $arrayData['repay_date'] = str_replace('-','/',$repay_date);
                $arrayData['loan_time'] = str_replace('-','/',$arrayData['loan_time']);
                $yihuanMoney = RepaySummary::getYihuanMoney($value);
                //本金
                if (!$is_channel){
                    //非晋商的本金是公共平台扣的
                    $arrayData['yh_amount'] = $yihuanMoney['amount'];
                }else{
                    $arrayData['yh_amount'] = 0;
                }
                //期数
                if ($value->pay_off == RepayRecord::PAY_OFF_ONE){
                    $arrayData['current_period'] = $value->period;
                }else{
                    $arrayData['current_period'] =  $summaryInfo->period_count;
                }
                $commons_lending_money =  $value->common->lending_money;

                if(strtotime($value->common->apply_time) >= strtotime('2019-03-28 07:15')){//白名单功能 2019-03-28 07:10上线
                    $arrayData['yh_all_money'] = $arrayData['yh_amount']  + $yihuanMoney['common_repayment_money'] + $commons_lending_money + $yihuanMoney['common_fines_money']+$yihuanMoney['manage_money']+$yihuanMoney['month_money']- $yihuanMoney['discount_amount'];//已还总金额(本金+提前手续费+利息+罚息+贷后+月保)
                }else{
                    $arrayData['yh_all_money'] = $arrayData['yh_amount']  + $yihuanMoney['common_repayment_money'] + $commons_lending_money + $yihuanMoney['common_fines_money']+$yihuanMoney['manage_money']+$yihuanMoney['month_money'];//已还总金额(本金+提前手续费+利息+罚息+贷后+月保)
                }
                $arrayData['yh_lending_money'] = $commons_lending_money;//已还平台利息
                $arrayData['yh_manage_money'] = $yihuanMoney['manage_money'];//已还贷后管理费
                $arrayData['yh_money_fines_money'] = $yihuanMoney['money_fines_money'];//已还资方罚息
                $arrayData['yh_common_fines_money'] = $yihuanMoney['common_fines_money'];//已还平台罚息
                $arrayData['yh_month_money'] = $yihuanMoney['month_money'];//已还其他费用(月保费)
                $arrayData['yh_money_repayment_money'] = $yihuanMoney['money_repayment_money'];//已还资方提前结清
                $arrayData['yh_common_repayment_money'] = $yihuanMoney['common_repayment_money'];//已还公共提前结清
                $arrayData['yh_month'] = $this->getHistoryMonth($value->summary_id);//已还总月保
                $arrayData['yh_balance'] = $this->getHistoryBalance($value->summary_id);//总余额
                $arrayData['discount_amount'] = bcmul($value->common->discount_amount,-1,2);//折扣金额,负数
                $arrayData['balance'] = $value->balance/100;//单期余额
                $arrayData['statustwo'] = $this->getRepayTypeTwo($value);//全部还款,部分还款
                if ($arrayData['yh_amount'] == 0 && $yihuanMoney['common_repayment_money'] == 0  &&  $commons_lending_money == 0 &&  $yihuanMoney['common_fines_money'] == 0  && $yihuanMoney['manage_money'] == 0 && $yihuanMoney['month_money'] == 0){
                    continue;
                }
                $excelData[] = $arrayData;
            }
        }
        $header = ['授信ID','产品','用户号','用户姓名','手机号','身份证号','借款订单号','渠道号','渠道来源','放款交易号','放款资方渠道','放款金额','分期期数','实收历史总金额','实收历史总本金','实收历史总利息','历史平台总贷后管理费','历史总资方罚息','历史总平台罚息','历史总月保费','历史总其他金额','还款交易号','还款渠道方','还款状态','还款第几期','预计还款日期','实际还款日期','实还款总金额','实还本金','实还利息','实还贷后管理费','实还平台罚息','实还平台提前结清手续费','实还月保费','实还其他费用(溢缴)','折扣金额','状态2','放款时间'];

        $fields = ['loan_request_id','product','user_id','name','phone_num','cert_number','loan_order_id','source_code','source_name','loan_id','channel_name','amount','period_count','history_all_money','history_amount','history_lending_money','history_manage_money','history_money_fines_money','history_common_fines_money','yh_month','yh_balance','trade_number','channel','repay_type','current_period','normal_date','repay_date','yh_all_money','yh_amount','yh_lending_money','yh_manage_money','yh_common_fines_money','yh_common_repayment_money','yh_month_money','balance','discount_amount','statustwo','loan_time'];

        $table_heard = ['amount'=>11,'period_count'=>12,'history_all_money'=>13,'history_amount'=>14,'history_lending_money'=>15,'history_manage_money'=>16,'history_money_fines_money'=>17,'history_common_fines_money'=>18,'yh_month'=>19,'yh_balance'=>20,'yh_all_money'=>27,'yh_amount'=>28,'yh_lending_money'=>29,'yh_manage_money'=>30,'yh_common_fines_money'=>31,'yh_common_repayment_money'=>32,'yh_month_money'=>33,'balance'=>34];
        $today = Carbon::today()->toDateString();
        $spread = new Spreadsheet();
        $sheet = $spread->getActiveSheet();
        //设置header
        $len = count($header);
        for ($i = 0; $i < $len; $i++) {
            if ($i <= 25) {
                $sheet->setCellValue(chr(65 + $i) . "1", $header[$i]);
            } else {
                $n = floor($i / 26);
                $sheet->setCellValue(chr(65 + $n - 1) . chr(65 + ($i % 26)) . "1", $header[$i]);
            }
        }
        $len = count($fields);
        foreach ($excelData as $key => $item) {
            $row = 2 + $key;
            for ($i = 0; $i < $len; $i++) {
                if ($i <= 25) {
                    $position = chr(65 + $i) . $row;
                } else {
                    $n = floor($i / 26);
                    $position = chr(65 + $n - 1) . chr(65 + ($i % 26)) . $row;
                }
                $sheet->setCellValueExplicit($position, $item[$fields[$i]], DataType::TYPE_STRING);
                if (in_array($i,$table_heard)){
                    $sheet->setCellValueExplicit($position, $item[ array_search($i,$table_heard)], DataType::TYPE_NUMERIC);
                }
            }
        }

        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spread);
        $file = '平台还款数据表' . $repay_date . '.xlsx';
        $filePath = storage_path($file);
        $writer->save($filePath);
        Mail::to($this->repaymentMail)->send(new RepayMail($filePath));
        $this->info($file . ' export success!!');
//        dump($filePath);
        unlink($filePath);
    }


    /**
     * Description:平台报表
     * Author:zhaiyukang
     */
    public function commontable(){
        $t0 = microtime(true);
        $time = $this->option('time');
        $repay_date = Carbon::yesterday()->toDateString();
        if(!empty($time)){
            $repay_date = $time;
        }
        $back = new RepayRecord();
        $back->setConnection('backend');
        $info = $back->query()
            ->from('repay_records as records')
            ->leftjoin('repay_record_moneys as moneys','records.id','moneys.record_id')
            ->leftjoin('repay_record_months as months','records.id','months.record_id')
            ->leftjoin('repay_record_commons as commons','records.id','commons.record_id')
            ->leftjoin('repay_summaries as summaries','records.summary_id','summaries.id')
            ->leftjoin('loan_apply as apply','apply.loan_request_id','summaries.loan_request_id')
            ->leftjoin('user_loan_requests as requests','requests.id','summaries.loan_request_id')
//            ->leftjoin('users','users.id','records.uid')
//            ->leftjoin('user_cert_ocrs as ocrs','users.id','=','ocrs.user_id')
//            ->groupBy('ocrs.user_id')
            ->orWhere(function ($q) use($repay_date){
                $q->where('records.status',RepayRecord::STATUS_FOUR)->where('pay_off',RepayRecord::PAY_OFF_ONE)->whereDate('records.repay_date',$repay_date);
            })
            ->orWhere(function ($q) use($repay_date){
                $q->where('records.period',0)
                    ->where('records.status',RepayRecord::STATUS_FOUR)
                    ->where('records.pay_off',RepayRecord::PAY_OFF_TWO)
                    ->whereDate('records.repay_date',$repay_date);
            })
            ->select(
                'requests.credit_id', //授信ID
                'summaries.loan_id as loid',//放款交易号
                'summaries.loan_request_id',
                //借款ID
                'records.uid',//uid
//                'ocrs.name',//用户姓名
//                'users.phone_num',//用户手机号
//                'ocrs.num as cert_number',//用户身份证号
                'summaries.repay_type as summary_repay_type',//产品名称
                'summaries.data_type',//新老数据
                'summaries.loan_channel',//放款资方渠道
                'requests.channel',//放款资方渠道
                'apply.out_trade_id',//借款订单号
                'summaries.amount as summary_amount',//放款金额
                'summaries.period_count',//分期期数
                'commons.pay_order_no as common_pay_order_no',//放款交易号
                'records.normal_date',//账单日=>预计还款日期
                'records.repay_type',//还款日=>实际还款日期
                'commons.period as common_period',//还款第几期

                'records.amount as record_amount',//实还本金
                'commons.lending_money as common_lending_money',//公共的利息
                'commons.manage_money as common_manage_money',//公共的管理费
                'commons.fines_money as common_fines_money',//公共的罚息
                'commons.repayment_money as common_repayment_money',//公共的提前结清手续费
                'months.amount as month_amount',//月保费
                'records.balance',//溢缴金额
                'commons.relief_amount',//减免金额
                'commons.discount_amount',//折扣金额
                'apply.loan_time',//放款时间

                'records.repay_type',
                'records.pay_off',
                'commons.third_record_id as common_third_record_id',//
                'commons.trade_no as common_trade_no',//
                'commons.apply_time as common_apply_time',//
                'commons.discount_amount as common_discount_amount'
            )
            ->get();
        $excelData = array();
        if(!empty($info)){
            foreach ($info as $k => $value){
                $is_channel = $value->data_type == RepaySummary::DATA_TYPE_ONE || $value->loan_channel == SettingChannel::CHANNEL_OLD_JINSHANG;
                if($is_channel){
                    //晋商,新光大本金是不扣的,所以判断要扣的金额是否为0,为0则跳出
                    $total = $value->common_lending_money + $value->common_fines_money + $value->common_manage_money + $value->common_repayment_money;
                    if($total == 0){
                        continue;
                    }
                }
                $arrayData = array();
                $arrayData['loan_request_id'] = $value->credit_id;
                $arrayData['loan_id'] = $value->loid;
                $arrayData['jiekuan_id'] = '';
                $userInfo = $this->getUserInfo($value->uid);
                $arrayData['name'] = $userInfo->name;//用户姓名
                $arrayData['phone_num'] = $userInfo->phone_num;//手机号
                $arrayData['cert_number'] = !empty($userInfo->cert_no)?$userInfo->cert_no:$userInfo->cert_number;//身份证号
                $arrayData['product'] = $this->getRepayName($value->summary_repay_type);//产品
                $channel_name = $this->getChannelNames($value);//放款资方渠道
                $arrayData['channel_name'] = $channel_name;//放款资方渠道
                $arrayData['period_count'] = $value->period_count;//分期期数
                $arrayData['loan_order_id'] = isset($value->out_trade_id) ? $value->out_trade_id : '';//借款订单号
                $arrayData['amount'] = bcdiv($value->summary_amount,100,2);//放款金额

                $arrayData['trade_number'] = isset($value->common_pay_order_no) ? $value->common_pay_order_no : '';//还款交易号
                if($repay_date == '2019-03-17' || $repay_date == '2019-03-18'){
                    if(!empty($value->common_trade_no)){
                        $data = [
                            'channelId' => 'JD_WITHHOLD', //支付渠道 
                            'orderId' => $value->common_trade_no, //用户流水号 ;
                        ];
                        $res = CommonPlatformRequestFacade::getCommonRepayResult($data);
                        if($res['data']['status'] != 1){
                            continue;
                        }else{
                            $arrayData['trade_number'] = $res['data']['payOrderNo'];
                        }

                    }
                }
                if(empty($value->common_pay_order_no) && empty($value->common_third_record_id) && empty($value->common_trade_no)){
                    continue;
                }

                $channel = $is_channel == true ? RepayRecordCommon::NEW_COMMON_MER : RepayRecordCommon::OLD_COMMON_MER;
                if($value->repay_type == RepayRecord::REPAY_TYPE_ACCOUNT){
                    $arrayData['channel'] = '线下还款';
                    $arrayData['trade_number'] = '';
                }else{
                    $arrayData['channel'] =$channel == RepayRecordCommon::NEW_COMMON_MER ? '联仲代扣' : '蔚商代扣';
                }
                $arrayData['repay_type'] = $this->getRepayTypeold($value);
                //期数
                if ($value->pay_off == RepayRecord::PAY_OFF_ONE){
                    $arrayData['current_period'] = $value->common_period;
                }else{
                    $arrayData['current_period'] =  $value->period_count;
                }
                $arrayData['normal_date'] = str_replace('-','/',$value->normal_date);
                $arrayData['repay_date'] = str_replace('-','/',$repay_date);

                if (!$is_channel){
                    //非晋商的本金是公共平台扣的
                    $yh_amount = $value->record_amount;
                }else{
                    $yh_amount = 0;
                }

                $commons_lending_money =  $value->common_lending_money;

                if(strtotime($value->common_apply_time) >= strtotime('2019-03-28 07:15')){//白名单功能 2019-03-28 07:10上线
                    $yh_all_money = $yh_amount  + $value->common_repayment_money + $commons_lending_money + $value->common_fines_money+$value->common_manage_money-$value->discount_amount;//已还总金额(本金+提前手续费+利息+罚息+贷后+月保)
                }else{
                    $yh_all_money = $yh_amount  + $value->common_repayment_money + $commons_lending_money + $value->common_fines_money+$value->common_manage_money;//已还总金额(本金+提前手续费+利息+罚息+贷后+月保)
                }
                $arrayData['yh_amount'] = bcdiv($yh_amount,100,2);
                $arrayData['yh_all_money'] = bcdiv($yh_all_money,100,2);
                $arrayData['yh_lending_money'] = bcdiv($commons_lending_money,100,2);//已还平台利息
                $arrayData['yh_manage_money'] = bcdiv($value->common_manage_money,100,2);//已还贷后管理费
                $arrayData['yh_common_fines_money'] = bcdiv($value->common_fines_money,100,2);//已还平台罚息
                $arrayData['yh_common_repayment_money'] = bcdiv($value->common_repayment_money,100,2);//已还公共提前结清
                $arrayData['month_money'] = bcdiv($value->month_amount,100,2);//已还其他费用(月保费)
                $arrayData['discount_amount'] = bcdiv(bcmul($value->common_discount_amount,-1,2),100,2);//折扣金额,负数
                $arrayData['balance'] = bcdiv($value->balance, 100, 2);//单期余额
                if ($value->status == RepayRecord::STATUS_FOUR){
                    $msg = '全部还款';
                }else{
                    $msg = '部分还款';
                }
                $arrayData['statustwo'] = $msg;//全部还款,部分还款

                if ($arrayData['yh_amount'] == 0 && $value->common_repayment_money == 0  &&  $commons_lending_money == 0 &&  $value->common_fines_money == 0  && $value->common_manage_money == 0){
                    continue;
                }

                $arrayData['loan_time'] = isset($value->loan_time) ? $value->loan_time : '';//放款时间
                $excelData[] = $arrayData;
            }
        }
        $header = ['授信ID','放款交易号','借款ID','产品','用户姓名','手机号','身份证号','借款订单号','放款资方渠道','放款金额','分期期数','还款交易号','还款渠道方','还款状态','还款第几期','预计还款日期','实际还款日期','实还款总金额','实还本金','实还利息','实还贷后管理费','实还平台罚息','实还平台提前结清手续费','实还月保费','实还其他费用(溢缴)','折扣金额','状态2','放款时间'];

        $fields = ['loan_request_id','loan_id','jiekuan_id','product','name','phone_num','cert_number','loan_order_id','channel_name','amount','period_count','trade_number','channel','repay_type','current_period','normal_date','repay_date','yh_all_money','yh_amount','yh_lending_money','yh_manage_money','yh_common_fines_money','yh_common_repayment_money','month_money','balance','discount_amount','statustwo','loan_time'];

       //$table_heard = ['amount'=>11,'period_count'=>12,'yh_all_money'=>27,'yh_amount'=>28,'yh_lending_money'=>29,'yh_manage_money'=>30,'yh_common_fines_money'=>31,'yh_common_repayment_money'=>32,'month_money'=>33,'balance'=>34];
        $table_heard = [];
//        $today = Carbon::today()->toDateString();
        $spread = new Spreadsheet();
        $sheet = $spread->getActiveSheet();
        //设置header
        $len = count($header);
        for ($i = 0; $i < $len; $i++) {
            if ($i <= 25) {
                $sheet->setCellValue(chr(65 + $i) . "1", $header[$i]);
            } else {
                $n = floor($i / 26);
                $sheet->setCellValue(chr(65 + $n - 1) . chr(65 + ($i % 26)) . "1", $header[$i]);
            }
        }
        $len = count($fields);
        foreach ($excelData as $key => $item) {
            $row = 2 + $key;
            for ($i = 0; $i < $len; $i++) {
                if ($i <= 25) {
                    $position = chr(65 + $i) . $row;
                } else {
                    $n = floor($i / 26);
                    $position = chr(65 + $n - 1) . chr(65 + ($i % 26)) . $row;
                }
                $sheet->setCellValueExplicit($position, $item[$fields[$i]], DataType::TYPE_STRING);
                if (in_array($i,$table_heard)){
                    $sheet->setCellValueExplicit($position, $item[ array_search($i,$table_heard)], DataType::TYPE_NUMERIC);
                }
            }
        }

        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spread);
        $file = '(新版)平台还款数据表' . $repay_date . '.xlsx';
        $filePath = storage_path($file);
        $writer->save($filePath);
//        Mail::to($this->repaymentMail)->send(new RepayMail($filePath));
//        $this->info($file . ' export success!!');
        dump($filePath);
        $t1 = microtime(true);
        dd($t1-$t0);
//        unlink($filePath);
    }



    /**
     * 资方报表
     * */
    public function  repaymentmoney(){
        //查询昨天的还款成功记录
        $time = $this->option('time');
        $repay_date = Carbon::yesterday()->toDateString();
        if(!empty($time)){
            $repay_date = $time;
        }

        $back = new RepayRecord();
        $back->setConnection('backend');
        $info = $back->query()
            ->from('repay_records')
            ->select('repay_records.*')
            ->leftjoin('repay_record_moneys','repay_record_moneys.record_id','repay_records.id')
            ->where('repay_record_moneys.status',RepayRecordMoney::STATUS_FOUR)
            ->where('repay_record_moneys.period','<>',0)
            ->where('repay_record_moneys.repay_date',$repay_date)
            ->where('repay_records.pay_off',RepayRecord::PAY_OFF_ONE)
            ->get();
        if (!empty($info)){
            $summaryArray = array();
            $excelData = array();
            foreach ($info as $k => $value){

                $is_channel = RepaySummary::getLoanChannel($value->summary_id);
                if (!$is_channel){
                    //老光大,农信,不取资方表
                    continue;
                }
                if($value->money->type == 4){
                    continue;
                }
                $summaryInfo = $value->relationSummary;
                $loanApply = LoanApply::select('out_trade_id','loan_time')->where('loan_request_id',$summaryInfo->loan_request_id)->first();//借款订单号
                $userLoanRequest = UserLoanRequest::find($summaryInfo->loan_request_id);//授信ID
                $arrayData = array();
                $arrayData['loan_request_id'] = $userLoanRequest->credit_id;//授信ID
                $arrayData['product'] = $this->getRepayName($summaryInfo->repay_type);//产品
                $userInfo = $this->getUserInfo($value->uid);
                $arrayData['user_id'] = $userInfo->nfsp_user_id;//用户号
                $arrayData['name'] = $userInfo->name;//用户姓名
                $arrayData['phone_num'] = $userInfo->phone_num;//手机号
                $arrayData['cert_number'] = !empty($userInfo->cert_no)?$userInfo->cert_no:$userInfo->cert_number;//身份证号
                $arrayData['loan_order_id'] = isset($loanApply->out_trade_id) ? $loanApply->out_trade_id : '';//借款订单号
                $arrayData['loan_time'] = isset($loanApply->loan_time) ? $loanApply->loan_time : '';//借款订单号
                $arrayData['source_code'] = $userInfo->source;//渠道号
                $arrayData['source_name'] = '';//渠道来源
                $arrayData['loan_id'] = $summaryInfo->loan_id;//放款交易号

                $channel_name = $this->getChannelNames($summaryInfo);
                $arrayData['channel_name'] = $channel_name;//放款资方渠道
                $arrayData['amount'] = $summaryInfo->amount;//放款金额
                $arrayData['period_count'] = $summaryInfo->period_count;//分期期数
                $historyMoney = $this->getHistoryMoney($value,$summaryInfo);//获取历史各种金额
                $arrayData['history_all_money'] = $historyMoney['history_all_money'];//历史总金额
                $arrayData['history_amount'] = $historyMoney['history_amount'];//历史总本金
                $arrayData['history_lending_money'] = $historyMoney['history_lending_money'];//历史总利息
                $arrayData['history_manage_money'] = $historyMoney['history_manage_money'];//历史总贷后管理费
                $arrayData['history_money_fines_money'] = $historyMoney['history_money_fines_money'];//历史总资方罚息
                $arrayData['history_common_fines_money'] = $historyMoney['history_common_fines_money'];//历史总平台罚息
                $arrayData['history_other_money'] = $historyMoney['history_other_money'];//历史总其他金额
                $arrayData['trade_number'] = isset($value->money->pay_order_no) ? $value->money->pay_order_no : '';//还款交易号


                $channel = $this->getChannelType($value);
                if($channel){
                    //晋商是没有这个pay_order_no
                    $arrayData['channel'] = '晋商代扣';//晋商为  晋商代扣,其他的为新流代扣
                }else{
                    $arrayData['channel'] = '新流代扣';//晋商为  晋商代扣,其他的为新流代扣
                }

                if(!empty($arrayData['trade_number'])){
                    $arrayData['channel'] = '新流代扣';       //晋商为  晋商代扣,其他的为新流代扣 
                }

                if($value->repay_type == RepayRecord::REPAY_TYPE_ACCOUNT){
                    $arrayData['channel'] = '线下还款';
                    $arrayData['trade_number'] = '';
                }



                $arrayData['repay_type'] = $this->getRepayType($value);//还款状态

                $arrayData['normal_date'] = str_replace('-','/',$value->normal_date);
                $arrayData['repay_date'] = str_replace('-','/',$repay_date);
                $arrayData['loan_time'] = str_replace('-','/',$arrayData['loan_time']);

                $arrayData['yh_all_money'] = bcadd(bcadd(bcadd($value->amount,$value->money->repayment_money,2),$value->money->lending_money,2),$value->money->fines_money,2);//已还总金额(本金+提前手续费+利息+罚息)
                $arrayData['yh_amount'] = $value->amount;//已还本金
                $arrayData['yh_money_fines_money'] = $value->money->fines_money;//已还资方罚息
                $arrayData['yh_month_money'] = $value->month->amount;//已还其他费用(月保费)
                $arrayData['yh_money_repayment_money'] = $value->money->repayment_money;//已还资方提前结清
                $arrayData['yh_manage_money'] = 0;//已还贷后管理费,资方没有管理费,=为0
                $arrayData['yh_lending_money'] = $value->money->lending_money;//已还利息
                $arrayData['current_period'] = $value->period; //当前期数
                $arrayData['yh_month'] = $this->getHistoryMonth($value->summary_id);//已还总月保
                $arrayData['yh_balance'] = $this->getHistoryBalance($value->summary_id);//总余额
                $arrayData['balance'] = $value->balance/100;//单期余额
                $arrayData['statustwo'] = $this->getRepayTypeTwo($value);//状态2
                $excelData[] = $arrayData;
            }
        }
        $back = new RepayRecord();
        $back->setConnection('backend');
        $info = $back->query()
            ->from('repay_records')
            ->select('repay_records.*')
            ->leftjoin('repay_record_moneys','repay_record_moneys.record_id','repay_records.id')
            ->where('repay_record_moneys.status',RepayRecordMoney::STATUS_FOUR)
            ->where('repay_record_moneys.period',0)
            ->where('repay_record_moneys.repay_date',$repay_date)
            ->where('repay_records.pay_off',RepayRecord::PAY_OFF_TWO)
            ->get();
        if (!empty($info)){
            foreach ($info as $k => $value){
                $is_channel = RepaySummary::getLoanChannel($value->summary_id);
                if (!$is_channel){
                    //老光大,农信,不取资方表
                    continue;
                }
                $summaryInfo = $value->relationSummary;
                $loanApply = LoanApply::select('out_trade_id','loan_time')->where('loan_request_id',$summaryInfo->loan_request_id)->first();//借款订单号
                $userLoanRequest = UserLoanRequest::find($summaryInfo->loan_request_id);//授信ID
                $arrayData = array();
                $arrayData['loan_request_id'] = $userLoanRequest->credit_id;//授信ID
                $arrayData['product'] = $this->getRepayName($summaryInfo->repay_type);//产品

                $userInfo = $this->getUserInfo($value->uid);
                $arrayData['user_id'] = $userInfo->nfsp_user_id;//用户号
                $arrayData['name'] = $userInfo->name;//用户姓名
                $arrayData['phone_num'] = $userInfo->phone_num;//手机号
                $arrayData['cert_number'] = !empty($userInfo->cert_no)?$userInfo->cert_no:$userInfo->cert_number;//身份证号

                $arrayData['loan_order_id'] = isset($loanApply->out_trade_id) ? $loanApply->out_trade_id : '';//借款订单号
                $arrayData['loan_time'] = isset($loanApply->loan_time) ? $loanApply->loan_time : '';//借款订单号
                $arrayData['source_code'] = $userInfo->source;//渠道号
                $arrayData['source_name'] = '';//渠道来源
                $arrayData['loan_id'] = $summaryInfo->loan_id;//放款交易号
                $channel_name = $this->getChannelNames($summaryInfo);
                $arrayData['channel_name'] = $channel_name;//放款资方渠道
                $arrayData['amount'] = $summaryInfo->amount;//放款金额
                $arrayData['period_count'] = $summaryInfo->period_count;//分期期数
                $historyMoney = $this->getHistoryMoney($value,$summaryInfo);//获取历史各种金额
                $arrayData['history_all_money'] = $historyMoney['history_all_money'];//历史总金额
                $arrayData['history_amount'] = $historyMoney['history_amount'];//历史总本金
                $arrayData['history_lending_money'] = $historyMoney['history_lending_money'];//历史总利息
                $arrayData['history_manage_money'] = $historyMoney['history_manage_money'];//历史总贷后管理费
                $arrayData['history_money_fines_money'] = $historyMoney['history_money_fines_money'];//历史总资方罚息
                $arrayData['history_common_fines_money'] = $historyMoney['history_common_fines_money'];//历史总平台罚息
                $arrayData['history_other_money'] = $historyMoney['history_other_money'];//历史总其他金额
                $arrayData['trade_number'] = isset($value->money->pay_order_no) ? $value->money->pay_order_no : '';//还款交易号

                $channel = $this->getChannelType($value);
                if($channel){
                    //晋商是没有这个pay_order_no
                    $arrayData['channel'] = '晋商代扣';//晋商为  晋商代扣,其他的为新流代扣
                }else{
                    $arrayData['channel'] = '新流代扣';//晋商为  晋商代扣,其他的为新流代扣
                }
                //如果有商户订单号，那就是新流代扣
                if(!empty($arrayData['trade_number'])){
                    $arrayData['channel'] = '新流代扣';
                }

                if($value->repay_type == RepayRecord::REPAY_TYPE_ACCOUNT){
                    $arrayData['channel'] = '线下还款';
                    $arrayData['trade_number'] = '';
                }

                $arrayData['repay_type'] = $this->getRepayType($value);//还款状态

                $arrayData['normal_date'] = str_replace('-','/',$value->normal_date);
                $arrayData['repay_date'] = str_replace('-','/',$repay_date);
                $arrayData['loan_time'] = str_replace('-','/',$arrayData['loan_time']);

                $arrayData['yh_all_money'] = bcadd(bcadd(bcadd($value->amount,$value->money->repayment_money,2),$value->money->lending_money,2),$value->money->fines_money,2);//已还总金额(本金+提前手续费+利息+罚息)
                $arrayData['yh_amount'] = $value->amount;//已还本金
                $arrayData['yh_money_fines_money'] = $value->money->fines_money;//已还资方罚息
                $arrayData['yh_month_money'] = $value->month->amount;//已还其他费用(月保费)
                $arrayData['yh_money_repayment_money'] = $value->money->repayment_money;//已还资方提前结清
                $arrayData['yh_manage_money'] = 0;//已还贷后管理费,资方没有管理费,=为0
                $arrayData['yh_lending_money'] = $value->money->lending_money;//已还利息
                $arrayData['current_period'] = $value->period; //当前期数
                $arrayData['yh_month'] = $this->getHistoryMonth($value->summary_id);//已还总月保
                $arrayData['yh_balance'] = $this->getHistoryBalance($value->summary_id);//总余额
                $arrayData['balance'] = $value->balance/100;//单期余额
                $arrayData['statustwo'] = $this->getRepayTypeTwo($value);//状态2
                $excelData[] = $arrayData;
            }
        }


        $header = ['授信ID','产品','用户号','用户姓名','手机号','身份证号','借款订单号','渠道号','渠道来源','放款交易号','放款资方渠道','放款金额','分期期数','实收历史总金额','实收历史总本金','实收历史总利息','历史平台总贷后管理费','历史总资方罚息','历史总平台罚息','历史总月保费','历史总其他金额','还款交易号','还款渠道方','还款状态','还款第几期','预计还款日期','实际还款日期','实还款总金额','实还本金','实还利息','实还资方罚息','实还资方提前结清手续费','状态2','放款时间'];

        $fields = ['loan_request_id','product','user_id','name','phone_num','cert_number','loan_order_id','source_code','source_name','loan_id','channel_name','amount','period_count','history_all_money','history_amount','history_lending_money','history_manage_money','history_money_fines_money','history_common_fines_money','yh_month','yh_balance','trade_number','channel','repay_type','current_period','normal_date','repay_date','yh_all_money','yh_amount','yh_lending_money','yh_money_fines_money','yh_money_repayment_money','statustwo','loan_time'];

        $table_heard = ['amount'=>11,'period_count'=>12,'history_all_money'=>13,'history_amount'=>14,'history_lending_money'=>15,'history_manage_money'=>16,'history_money_fines_money'=>17,'history_common_fines_money'=>18,'yh_month'=>19,'yh_balance'=>20,'yh_all_money'=>27,'yh_amount'=>28,'yh_lending_money'=>29,'yh_money_fines_money'=>30,'yh_money_repayment_money'=>31];
        $today = Carbon::today()->toDateString();
        $spread = new Spreadsheet();
        $sheet = $spread->getActiveSheet();
        //设置header
        $len = count($header);
        for ($i = 0; $i < $len; $i++) {
            if ($i <= 25) {
                $sheet->setCellValue(chr(65 + $i) . "1", $header[$i]);
            } else {
                $n = floor($i / 26);
                $sheet->setCellValue(chr(65 + $n - 1) . chr(65 + ($i % 26)) . "1", $header[$i]);
            }
        }
        $len = count($fields);
        foreach ($excelData as $key => $item) {
            $row = 2 + $key;
            for ($i = 0; $i < $len; $i++) {
                if ($i <= 25) {
                    $position = chr(65 + $i) . $row;
                } else {
                    $n = floor($i / 26);
                    $position = chr(65 + $n - 1) . chr(65 + ($i % 26)) . $row;
                }
                $sheet->setCellValueExplicit($position, $item[$fields[$i]], DataType::TYPE_STRING);
                if (in_array($i,$table_heard)){
                    $sheet->setCellValueExplicit($position, $item[ array_search($i,$table_heard)], DataType::TYPE_NUMERIC);
                }
            }
        }

        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spread);
        $file = '资方还款数据表' . $repay_date . '.xlsx';
        $filePath = storage_path($file);
        $writer->save($filePath);
//        dd($filePath);
        Mail::to($this->repaymentMail)->send(new RepayMoneyMail($filePath));
        $this->info($file . ' export success!!');
        unlink($filePath);

    }

    /**
     * 资方报表
     * */
    public function  moneytable(){
        //查询昨天的还款成功记录
        $time = $this->option('time');
        $repay_date = Carbon::yesterday()->toDateString();
        if(!empty($time)){
            $repay_date = $time;
        }

        $back = new RepayRecord();
        $back->setConnection('backend');
        $info = $back->query()
            ->from('repay_records as records')
            ->leftjoin('repay_record_moneys as money','money.record_id','records.id')
            ->leftjoin('repay_summaries as summaries','records.summary_id','summaries.id')
            ->leftjoin('loan_apply as apply','apply.loan_request_id','summaries.loan_request_id')
            ->leftjoin('user_loan_requests as requests','requests.id','summaries.loan_request_id')
            ->orWhere(function($q) use($repay_date){
                $q->where('money.status',RepayRecordMoney::STATUS_FOUR)
                    ->where('money.period','<>',0)
                    ->where('money.repay_date',$repay_date)
                    ->where('records.pay_off',RepayRecord::PAY_OFF_ONE);
            })
            ->orWhere(function($q) use($repay_date){
                $q->where('money.status',RepayRecordMoney::STATUS_FOUR)
                    ->where('money.period',0)
                    ->where('money.repay_date',$repay_date)
                    ->where('records.pay_off',RepayRecord::PAY_OFF_TWO);
            })
            ->select(
                'requests.credit_id', //授信ID
                'summaries.loan_id as loid',//放款交易号
                'summaries.loan_request_id',
                'records.summary_id',//summary_id
                //借款ID
                'records.uid',//uid
//                'ocrs.name',//用户姓名
//                'users.phone_num',//用户手机号
//                'ocrs.num as cert_number',//用户身份证号
                'summaries.repay_type as summary_repay_type',//产品名称
                'summaries.data_type',//新老数据
                'summaries.loan_channel',//放款资方渠道
                'requests.channel',//放款资方渠道
                'apply.out_trade_id',//借款订单号
                'summaries.amount as summary_amount',//放款金额
                'summaries.period_count',//分期期数
                'money.pay_order_no',//放款交易号
                'records.normal_date',//账单日=>预计还款日期
                'money.repay_type',//还款日=>实际还款日期
                'money.period as money_period',//还款第几期
                'money.type as money_type',//资方还款状态

                'records.amount as record_amount',//实还本金
                'money.lending_money as money_lending_money',//资方的利息
                'money.fines_money as money_fines_money',//资方的罚息
                'money.repayment_money as money_repayment_money',//资方的提前结清手续费
                'apply.loan_time',//放款时间

                'records.repay_type',
                'records.pay_off'
            )
            ->get();
        dd(count($info));
        if (!empty($info)){
            $summaryArray = array();
            $excelData = array();
            foreach ($info as $k => $value){

                $is_channel = RepaySummary::getLoanChannel($value->summary_id);

                if (!$is_channel){
                    //老光大,农信,不取资方表
                    continue;
                }
                if($value->money_type == 4){
                    continue;
                }
                $arrayData['loan_request_id'] = $value->credit_id;//授信ID
                $arrayData['product'] = $this->getRepayName($value->summary_repay_type);//产品
                $userInfo = $this->getUserInfo($value->uid);
                $arrayData['name'] = $userInfo->name;//用户姓名
                $arrayData['phone_num'] = $userInfo->phone_num;//手机号
                $arrayData['cert_number'] = !empty($userInfo->cert_no)?$userInfo->cert_no:$userInfo->cert_number;//身份证号
                $arrayData['loan_order_id'] = isset($value->out_trade_id )?$value->out_trade_id : '';//借款订单号
                $arrayData['loan_time'] = isset($value->loan_time) ? $value->loan_time : '';//借款订单号
                $arrayData['loan_id'] = $value->loid;//放款交易号
                $arrayData['jiekuan_id'] = '';//借款id
                $channel_name = $this->getChannelNames($value);
                $arrayData['channel_name'] = $channel_name;//放款资方渠道
                $arrayData['amount'] = bcdiv($value->summary_amount,100,2);//放款金额
                $arrayData['period_count'] = $value->period_count;//分期期数
                $arrayData['trade_number'] = isset($value->pay_order_no) ? $value->pay_order_no : '';//还款交易号

                $channel = $this->getChannelType($value);
                if($channel){
                    //晋商是没有这个pay_order_no
                    $arrayData['channel'] = '晋商代扣';//晋商为  晋商代扣,其他的为新流代扣
                }else{
                    $arrayData['channel'] = '新流代扣';//晋商为  晋商代扣,其他的为新流代扣
                }

                if(!empty($arrayData['trade_number'])){
                    $arrayData['channel'] = '新流代扣';       //晋商为  晋商代扣,其他的为新流代扣 
                }

                if($value->repay_type == RepayRecord::REPAY_TYPE_ACCOUNT){
                    $arrayData['channel'] = '线下还款';
                    $arrayData['trade_number'] = '';
                }

                $arrayData['repay_type'] = $this->getRepayTypeNew($value);//还款状态

                $arrayData['normal_date'] = str_replace('-','/',$value->normal_date);
                $arrayData['repay_date'] = str_replace('-','/',$repay_date);
                $arrayData['loan_time'] = str_replace('-','/',$arrayData['loan_time']);
                $yh_all_money = $value->amount+$value->money_repayment_money+$value->money_lending_money+$value->money_fines_money;
                $arrayData['yh_all_money'] = bcdiv($yh_all_money,100,2);//已还总金额(本金+提前手续费+利息+罚息)
                $arrayData['yh_amount'] = bcdiv($value->record_amount,100,2);//已还本金
                $arrayData['yh_money_fines_money'] = bcdiv($value->money_fines_money,100,2);//已还资方罚息
                $arrayData['yh_money_repayment_money'] = bcdiv($value->money_repayment_money,100,2);//已还资方提前结清
                $arrayData['yh_lending_money'] = bcdiv($value->money_lending_money,100,2);//已还利息
                $arrayData['current_period'] = $value->money_period; //当前期数
                $arrayData['balance'] = bcdiv($value->balance,100,2);//单期余额
                $arrayData['statustwo'] = $this->getRepayTypeTwo($value);//状态2
                $excelData[] = $arrayData;
            }
        }


        $header = ['授信ID','放款交易号','借款ID','产品','用户姓名','手机号','身份证号','借款订单号','放款交易号','放款资方渠道','放款金额','分期期数','还款交易号','还款渠道方','还款状态','还款第几期','预计还款日期','实际还款日期','实还款总金额','实还本金','实还利息','实还资方罚息','实还资方提前结清手续费','状态2','放款时间'];

        $fields = ['loan_request_id','loan_id','jiekuan_id','product','name','phone_num','cert_number','loan_order_id','loan_id','channel_name','amount','period_count','trade_number','channel','repay_type','current_period','normal_date','repay_date','yh_all_money','yh_amount','yh_lending_money','yh_money_fines_money','yh_money_repayment_money','statustwo','loan_time'];

        //$table_heard = ['amount'=>11,'period_count'=>12,'history_all_money'=>13,'history_amount'=>14,'history_lending_money'=>15,'history_manage_money'=>16,'history_money_fines_money'=>17,'history_common_fines_money'=>18,'yh_month'=>19,'yh_balance'=>20,'yh_all_money'=>27,'yh_amount'=>28,'yh_lending_money'=>29,'yh_money_fines_money'=>30,'yh_money_repayment_money'=>31];
        $table_heard = [];
        $spread = new Spreadsheet();
        $sheet = $spread->getActiveSheet();
        //设置header
        $len = count($header);
        for ($i = 0; $i < $len; $i++) {
            if ($i <= 25) {
                $sheet->setCellValue(chr(65 + $i) . "1", $header[$i]);
            } else {
                $n = floor($i / 26);
                $sheet->setCellValue(chr(65 + $n - 1) . chr(65 + ($i % 26)) . "1", $header[$i]);
            }
        }
        $len = count($fields);
        foreach ($excelData as $key => $item) {
            $row = 2 + $key;
            for ($i = 0; $i < $len; $i++) {
                if ($i <= 25) {
                    $position = chr(65 + $i) . $row;
                } else {
                    $n = floor($i / 26);
                    $position = chr(65 + $n - 1) . chr(65 + ($i % 26)) . $row;
                }
                $sheet->setCellValueExplicit($position, $item[$fields[$i]], DataType::TYPE_STRING);
                if (in_array($i,$table_heard)){
                    $sheet->setCellValueExplicit($position, $item[ array_search($i,$table_heard)], DataType::TYPE_NUMERIC);
                }
            }
        }

        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spread);
        $file = '(新版)资方还款数据表' . $repay_date . '.xlsx';
        $filePath = storage_path($file);
        $writer->save($filePath);
//        dd($filePath);
        Mail::to($this->repaymentMail)->send(new RepayMoneyMail($filePath));
        $this->info($file . ' export success!!');
        unlink($filePath);

    }

    /**
     * Description:龙信平台报表
     * Author:zhaiyukang
     */
    public function repaymentcommonoflongxin(){
        //查询昨天的还款成功记录
        $time = $this->option('time');
        $repay_date = Carbon::yesterday()->toDateString();
        if(!empty($time)){
            $plan_data = $repay_date = $time;
        }
        $inPlan = $outPlan = [];
        $back = new RepayRecord();
        $back->setConnection('backend');
        //以还款日期为准查询
        $info =$back->query()
            ->from('repay_records as records')
            ->with(['month','common','relationSummary'])
            ->orWhere(function ($q) use($repay_date){
                $q->where('records.status',RepayRecord::STATUS_FOUR)->where('pay_off',RepayRecord::PAY_OFF_ONE)->whereDate('records.repay_date',$repay_date);
            })
            ->orWhere(function ($q) use($repay_date){
                $q->where('records.period',0)
                    ->where('records.status',RepayRecord::STATUS_FOUR)
                    ->where('records.pay_off',RepayRecord::PAY_OFF_TWO)
                    ->whereDate('records.repay_date',$repay_date);
            })
            ->get();
        $excelData = array();
        if(!empty($info)){
            foreach ($info as $k => $value){
                $is_channel = RepaySummary::getLoanChannel($value->summary_id);
                if($is_channel){
                    //晋商,新光大本金是不扣的
                    $total = $value->common->lending_money + $value->common->fines_money + $value->common->manage_money + $value->common->repayment_money;
                    if($total == 0){
                        continue;
                    }
                }
                $summaryInfo = $value->relationSummary;
                $loanApply = LoanApply::select('out_trade_id','loan_time')->where('loan_request_id',$summaryInfo->loan_request_id)->first();
                $userLoanRequest = UserLoanRequest::select('credit_id')->find($summaryInfo->loan_request_id);
                $arrayData = array();
                $arrayData['record_id'] = $value->id;
                $arrayData['loan_request_id'] = $userLoanRequest->credit_id;
                $arrayData['product'] = $this->getRepayName($summaryInfo->repay_type);//产品
                $userInfo = $this->getUserInfo($value->uid);
                $arrayData['name'] = $userInfo->name;//用户姓名
                $arrayData['loan_time'] = isset($loanApply->loan_time) ? $loanApply->loan_time : '';//放款时间

                $longxin = ['LCC201709190006','LCC201709190007','LCC201709190011','LCC201709190008'];
                if(!in_array($summaryInfo->loan_channel,$longxin)){
                    continue;
                }
                $channel_name = $this->getChannelNames($summaryInfo);
                $arrayData['channel_name'] = $channel_name;//放款资方渠道
                $arrayData['amount'] = $summaryInfo->amount;//放款金额
                $arrayData['period_count'] = $summaryInfo->period_count;//分期期数
                $arrayData['current_amount'] = $value->amount;//计划还款金额
                $arrayData['lending_money'] = $value->common->lending_money;//计划还款利息

                if(empty($value->common->pay_order_no) && empty($value->common->third_record_id) && empty($value->common->trade_no)){
                    continue;
                }

                $arrayData['repay_type'] = $this->getRepayTypeold($value);
                $arrayData['normal_date'] = str_replace('-','/',$value->normal_date);
                $arrayData['repay_date'] = str_replace('-','/',$repay_date);

                $arrayData['loan_time'] = str_replace('-','/',$arrayData['loan_time']);

                $yihuanMoney = RepaySummary::getYihuanMoney($value);
                //本金
                if (!$is_channel){
                    //非晋商的本金是公共平台扣的
                    $arrayData['yh_amount'] = $yihuanMoney['amount'];
                }else{
                    $arrayData['yh_amount'] = 0;
                }
                //期数
                if ($value->pay_off == RepayRecord::PAY_OFF_ONE){
                    $arrayData['current_period'] = $value->period;
                }else{
                    $arrayData['current_period'] =  $summaryInfo->period_count;
                }
                $commons_lending_money =  $value->common->lending_money;

                if(strtotime($value->common->apply_time) >= strtotime('2019-03-28 07:15')){//白名单功能 2019-03-28 07:10上线
                    $arrayData['yh_all_money'] = $arrayData['yh_amount']  + $yihuanMoney['common_repayment_money'] + $commons_lending_money + $yihuanMoney['common_fines_money']+$yihuanMoney['manage_money']+$yihuanMoney['month_money']- $yihuanMoney['discount_amount'];//已还总金额(本金+提前手续费+利息+罚息+贷后+月保)
                }else{
                    $arrayData['yh_all_money'] = $arrayData['yh_amount']  + $yihuanMoney['common_repayment_money'] + $commons_lending_money + $yihuanMoney['common_fines_money']+$yihuanMoney['manage_money']+$yihuanMoney['month_money'];//已还总金额(本金+提前手续费+利息+罚息+贷后+月保)
                }
                $arrayData['yh_lending_money'] = $commons_lending_money;//已还平台利息

                if ($arrayData['yh_amount'] == 0 && $yihuanMoney['common_repayment_money'] == 0  &&  $commons_lending_money == 0 &&  $yihuanMoney['common_fines_money'] == 0  && $yihuanMoney['manage_money'] == 0 && $yihuanMoney['month_money'] == 0){
                    continue;
                }

                //计划内还款

                if($arrayData['normal_date'] == $arrayData['repay_date']){
                    $inPlan[] = $arrayData;
                }else{
                    $outPlan[] = $arrayData;
//                    $excelData[] = $arrayData;
                }

            }
        }


        //以计划还款日期为准查询
        $info =$back->query()
            ->from('repay_records as records')
            ->with(['month','common','relationSummary'])
            ->orWhere(function ($q) use($plan_data){
                $q->whereIn('records.status',[RepayRecord::STATUS_TWO,RepayRecord::STATUS_THREE])
                    ->where('records.pay_off',RepayRecord::PAY_OFF_ONE)
                    ->whereDate('records.normal_date',$plan_data);
            })
            ->get();
        $excelData = array();
        if(!empty($info)){
            foreach ($info as $k => $value){
                $is_channel = RepaySummary::getLoanChannel($value->summary_id);
                if($is_channel){
                    //晋商,新光大本金是不扣的
                    $total = $value->common->lending_money + $value->common->fines_money + $value->common->manage_money + $value->common->repayment_money;
                    if($total == 0){
                        continue;
                    }
                }
                $summaryInfo = $value->relationSummary;
                $loanApply = LoanApply::select('out_trade_id','loan_time')->where('loan_request_id',$summaryInfo->loan_request_id)->first();
                $userLoanRequest = UserLoanRequest::select('credit_id')->find($summaryInfo->loan_request_id);
                $arrayData = array();
                $arrayData['record_id'] = $value->id;
                $arrayData['loan_request_id'] = $userLoanRequest->credit_id;
                $arrayData['product'] = $this->getRepayName($summaryInfo->repay_type);//产品
                $userInfo = $this->getUserInfo($value->uid);
                $arrayData['name'] = $userInfo->name;//用户姓名
                $arrayData['loan_time'] = isset($loanApply->loan_time) ? $loanApply->loan_time : '';//放款时间

                $longxin = ['LCC201709190006','LCC201709190007','LCC201709190011','LCC201709190008'];
                if(!in_array($summaryInfo->loan_channel,$longxin)){
                    continue;
                }
                $channel_name = $this->getChannelNames($summaryInfo);
                $arrayData['channel_name'] = $channel_name;//放款资方渠道
                $arrayData['amount'] = $summaryInfo->amount;//放款金额
                $arrayData['period_count'] = $summaryInfo->period_count;//分期期数
                $arrayData['current_amount'] = $value->amount;//计划还款金额
                $arrayData['lending_money'] = $value->common->lending_money;//计划还款利息

                if(!empty($value->repay_date)){
                    continue;
                }
                $arrayData['repay_type'] = $this->getRepayTypeold($value);
                $arrayData['normal_date'] = str_replace('-','/',$value->normal_date);
                $arrayData['repay_date'] = '';
                $arrayData['loan_time'] = str_replace('-','/',$arrayData['loan_time']);

                $yihuanMoney = RepaySummary::getYihuanMoney($value);
                //本金
                if (!$is_channel){
                    //非晋商的本金是公共平台扣的
                    $arrayData['yh_amount'] = $yihuanMoney['amount'];
                }else{
                    $arrayData['yh_amount'] = 0;
                }
                //期数
                if ($value->pay_off == RepayRecord::PAY_OFF_ONE){
                    $arrayData['current_period'] = $value->period;
                }else{
                    $arrayData['current_period'] =  $summaryInfo->period_count;
                }

                $arrayData['yh_lending_money'] = 0;//已还平台利息

                $inPlan[] = $arrayData;
            }
        }

        $header = ['产品','用户姓名','放款资方渠道','放款金额','分期期数','还款第几期','放款时间','计划还款日期','计划还款本金','计划还款利息','还款状态','实际还款日期','实还本金','实还利息'];
        $fields = ['product','name','channel_name','amount','period_count','current_period','loan_time','normal_date','current_amount','lending_money','repay_type','repay_date','yh_amount','yh_lending_money'];
        for($i=0;$i<5;$i++){
            foreach($fields as $v){
                $arrayData[$v] = '';
            }
            $inPlan[] = $arrayData;
        }

        $excelData = array_merge($inPlan,$outPlan);
        //$table_heard = ['repay_date'=>11,'yh_amount'=>12,'yh_lending_money'=>13];
        $table_heard = [];
        $today = Carbon::today()->toDateString();
        $spread = new Spreadsheet();
        $sheet = $spread->getActiveSheet();
        //设置header
        $len = count($header);
        for ($i = 0; $i < $len; $i++) {
            if ($i <= 25) {
                $sheet->setCellValue(chr(65 + $i) . "1", $header[$i]);
            } else {
                $n = floor($i / 26);
                $sheet->setCellValue(chr(65 + $n - 1) . chr(65 + ($i % 26)) . "1", $header[$i]);
            }
        }
        $len = count($fields);
        foreach ($excelData as $key => $item) {
            $row = 2 + $key;
            for ($i = 0; $i < $len; $i++) {
                if ($i <= 25) {
                    $position = chr(65 + $i) . $row;
                } else {
                    $n = floor($i / 26);
                    $position = chr(65 + $n - 1) . chr(65 + ($i % 26)) . $row;
                }
                $sheet->setCellValueExplicit($position, $item[$fields[$i]], DataType::TYPE_STRING);
                if (in_array($i,$table_heard)){
                    $sheet->setCellValueExplicit($position, $item[ array_search($i,$table_heard)], DataType::TYPE_NUMERIC);
                }
            }
        }

        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spread);
        $file = '龙信平台还款数据表' . $repay_date . '.xlsx';
        $filePath = storage_path($file);
        $writer->save($filePath);
        Mail::to($this->repaymentMail)->send(new RepayMail($filePath));
        $this->info($file . ' export success!!');
//        dump($filePath);
        unlink($filePath);
    }




    public function getChannelType($recordInfo){
        $channel_name = '';
        $summaryInfo = $recordInfo->relationSummary;
        $requestInfo = UserLoanRequest::select('channel')->find($summaryInfo->loan_request_id);
        if($summaryInfo->data_type == RepaySummary::DATA_TYPE_ONE){
            //新数据
            if (!empty($requestInfo->channel) && $requestInfo->channel == SettingChannel::CHANNEL_JIXIAO) {
                return true;
            }else{
                return false;
            }
        }else{
            //老数据
            if($summaryInfo->loan_channel == SettingChannel::CHANNEL_OLD_JINSHANG){
                if (!empty($requestInfo->channel)){
                    if ($requestInfo->channel == SettingChannel::CHANNEL_GUANGDA){
                        return false;
                    }elseif($requestInfo->channel == SettingChannel::CHANNEL_JIXIAO){
                        return true;
                    }
                }else{
                    return true;
                }
            }else {
                return false;
            }
        }

    }


    /**
     * Description:还款状态
     * Author:zhaiyukang
     * @param $value
     * @return string
     */
    protected function getRepayTypeTwo($value){

        if ($value->status == RepayRecord::STATUS_FOUR){
            $msg = '全部还款';
        }else{
            $msg = '部分还款';
        }
        return $msg;
    }

    /**
     * Description:总月保费
     * Author:zhaiyukang
     * @param $summary_id
     * @return int
     */
    public function getHistoryMonth($summary_id)
    {
        $history_money = 0; //历史月保费
        foreach (RepayRecordMonth::where('summary_id', $summary_id)
                     ->where('status', RepayRecordMonth::STATUS_FOUR)
                     ->cursor() as $k => $value) {
            $history_money += $value->amount;

        }
        return $history_money;
    }

    /**
     * Description:产品名称
     * Author:zhaiyukang
     * @param $type
     * @return string
     */
    public function getRepayName($type){
        $msg='';
        switch ($type){
            case 0:
                $msg = '快闪卡贷2';
                break;
            case 1:
                $msg = '快闪卡贷';
                break;
            case 2:
                $msg = '快闪卡贷1';
                break;
            default:
                return '';
        }
        return $msg;
    }



    /**
     * Description:总余额
     * Author:zhaiyukang
     * @param $summary_id
     * @return float|int
     */
    public function getHistoryBalance($summary_id)
    {
        $history_balance = 0; //历史余额
        foreach (RepayRecord::where('summary_id', $summary_id)
                     ->where('status', RepayRecordMonth::STATUS_FOUR)
                     ->cursor() as $k => $value) {
            $history_balance += $value->balance;
        }
        return $history_balance / 100;
    }

    /**
     * Description:获取用户信息
     * Author:lijiafei
     * @param $user_id int
     * @return User
     */
    public function getUserInfo($user_id)
    {
        $info = User::select('users.id', 'nfsp_user_id', 'source', 'phone_num','name','num as cert_number')
            ->leftjoin('user_cert_ocrs','users.id','=','user_cert_ocrs.user_id')
            ->where('users.id', $user_id)->first();
//        $info->name = $info['name'];
//        $info->cert_number = $info['num'];
        return $info;
    }


    /**
     * Description:还款状态
     * Author:zhaiyukang
     * @param $value
     * @return string
     */
    protected function getRepayType($value){

        if ($value->pay_off == RepayRecord::PAY_OFF_TWO){
            $msg='提前结清';
            return $msg;
        }
        if ($value->normal_date == date('Y-m-d',strtotime($value->money->repay_date)) ){
            $msg='正常还款';
        }elseif ($value->normal_date > date('Y-m-d',strtotime($value->money->repay_date))){
            $msg='提前还款';
        }else{
            $msg='逾期还款';
        }
        return $msg;

    }

    /**
     * Description:还款状态
     * Author:zhaiyukang
     * @param $value
     * @return string
     */
    protected function getRepayTypeNew($value){

        if ($value->pay_off == RepayRecord::PAY_OFF_TWO){
            $msg='提前结清';
            return $msg;
        }
        if ($value->normal_date == date('Y-m-d',strtotime($value->repay_date)) ){
            $msg='正常还款';
        }elseif ($value->normal_date > date('Y-m-d',strtotime($value->repay_date))){
            $msg='提前还款';
        }else{
            $msg='逾期还款';
        }
        return $msg;

    }

    protected  function getRepayTypeold($value){
        if(!empty($value->repay_date)){
            if ($value->pay_off == RepayRecord::PAY_OFF_TWO ){
                $msg='提前结清';
            }elseif ( $value->normal_date == $value->repay_date){
                $msg='正常还款';
            }elseif ($value->normal_date > $value->repay_date){
                $msg='提前还款';
            }elseif ($value->normal_date < $value->repay_date){
                $msg='逾期还款';
            }else{
                $msg = '';
            }
        }else{
            $msg='逾期未还';
        }
        return $msg;
    }

    /**
     * Description:获取放款资方渠道名称
     * Author:lijiafei
     * @param $loan_request_id int user_loan_request表的id
     * @return mixed string 资方名称
     */
    protected function getChannelNames($summaryInfo)
    {
        $channel_name = '';
        $requestInfo = UserLoanRequest::select('channel')->find($summaryInfo->loan_request_id);
        if($summaryInfo->data_type == RepaySummary::DATA_TYPE_ONE){
            //新数据
            if (!empty($requestInfo->channel)) {
                $channel_name = SettingChannel::getChannelNameByChannel($requestInfo->channel);
                $channel_name = '新-' . $channel_name;
            }
        }else{
            //老数据
            if($summaryInfo->loan_channel == SettingChannel::CHANNEL_OLD_JINSHANG){
                if (!empty($requestInfo->channel)){
                    if ($requestInfo->channel == SettingChannel::CHANNEL_GUANGDA){
                        $channel_name = '老-新光大';
                    }elseif($requestInfo->channel == SettingChannel::CHANNEL_JIXIAO){
                        $channel_name = '老-晋商';
                    }
                }else{
                    $channel_name = '老-晋商';
                }
            }else {
                //从loan_channel表中取出时哪个资方
                $channel_name = SettingChannel::$CHANNEL_LIST[$summaryInfo->loan_channel];
            }
        }

        return $channel_name;
    }

    /**
     * Description:算出来历史总金额
     * Author:lijiafei
     * @param RepaySummary $summaryInfo
     * @return array
     */
    protected function getHistoryMoney(RepayRecord $recordInfo, RepaySummary $summaryInfo)
    {
        //查找历史的已经还清的
        $period = $recordInfo->period;
        $history_all_money = 0;//历史总金额
        $history_amount = 0;//历史总本金
        $history_lending_money = 0;//历史总利息
        $history_manage_money = 0;//贷后管理费
        $history_other_money = 0;//其他费用 月保费
        $history_money_fines_money = 0;//历史资方罚息
        $history_common_fines_money = 0;//历史平台罚息
        foreach (RepayRecord::where('summary_id', $recordInfo->summary_id)
                     ->where('period', "<>", 0)
                     ->where('pay_off', RepayRecord::PAY_OFF_ONE)
                     ->where('status', RepayRecord::STATUS_FOUR)
                     ->cursor() as $k => $value) {
            $money = RepaySummary::getYihuanMoney($value);
            $history_all_money += $money['all_money'];//总金额
            $history_amount += $money['amount'];
            $history_lending_money += $money['lending_money'];
            $history_manage_money += $money['manage_money'];
            $history_other_money += $money['month_money'];
            $history_money_fines_money += $money['money_fines_money'];
            $history_common_fines_money += $money['common_fines_money'];

        }
        if (0 == $period) {
            //当前这条记录是全部结清
            $money = RepaySummary::getYihuanMoney($recordInfo);
            $history_all_money += $money['all_money'];//总金额
            $history_amount += $money['amount'];
            $history_lending_money += $money['lending_money'];
            $history_manage_money += $money['manage_money'];
            $history_other_money += $money['month_money'];
            $history_money_fines_money += $money['money_fines_money'];
            $history_common_fines_money += $money['common_fines_money'];
        }
        $return = [
            'history_all_money' => $history_all_money,
            'history_amount' => $history_amount,
            'history_lending_money' => $history_lending_money,
            'history_manage_money' => $history_manage_money,
            'history_other_money' => $history_other_money,
            'history_money_fines_money' => $history_money_fines_money,
            'history_common_fines_money' => $history_common_fines_money,

        ];
        return $return;
    }

    /**
     * Description:逾期1天
     * Author:Mmy
     */
    public function overdueOne()
    {
        $beforeDay = Carbon::today()->subDays(1)->toDateString();
        $to_mail = ['cw@vjwealth.com'];
        $this->getOverDueOneData($beforeDay, $to_mail);
    }

    public function getOverDueOneData($beforeDay, $to_mail)
    {
        ini_set('max_execution_time', 0);
        ini_set('memory_limit', '1024M');
        //查找当前逾期的没有还的记录
        $recordInfo = RepayRecord::with(['relationSummary' => function ($q) {
            $q->where('repay_summaries.status', RepaySummary::STATUS_ONE)
                ->where('repay_summaries.current_repay', RepaySummary::CURRENT_REPAY_ONE);
        },
            'relationSummary.relationLoanApply' => function ($q) {
                $q->whereIn('loan_status', [LoanApply::LOAN_STATUS_SUCCESS, LoanApply::LOAN_HANDLE_CHANGE_CARD_SUCCESS])
                    ->select('out_trade_id', 'loan_request_id');
            },
        ],
            'relationSummary.relationUserLoanRequests:id,credit_id',
            'money',
            'month',
            'common',
            'users:id,phone_num,nfsp_user_id,source'
        )
            ->where('status', '<>', RepayRecord::STATUS_FOUR)
            ->where('pay_off', RepayRecord::PAY_OFF_ONE)
            ->whereDate('normal_date', $beforeDay)
            ->orderBy('period')
            ->select('id', 'uid', 'summary_id', 'period', 'amount', 'status', 'pay_off', 'normal_date');

        $excelDataMoney = $excelDataCommon = array();
        $all_channel = SettingChannel::pluck('channel_name', 'channel_code')->toArray();
        $recordInfo->chunk(200, function ($recordInfo) use ($all_channel, &$excelDataMoney, &$excelDataCommon) {
            foreach ($recordInfo as $k => $record) {
                dump($k);
                $moneyData = ['all_money' => 0, 'ys_amount' => 0, 'lending_money' => 0, 'money_fines_money' => 0, 'amount_lending_money' => 0];
                $commonData = ['all_money' => 0, 'common_fines_money' => 0, 'manage_money' => 0, 'month_money' => 0, 'ys_amount' => 0, 'lending_money' => 0];
                $data['record_id'] = $record->id;
                $data['credit_id'] = isset($record->relationSummary->relationUserLoanRequests) ? $record->relationSummary->relationUserLoanRequests->credit_id : '';
                $data['product'] = $this->repayType($record->relationSummary->repay_type);//产品
                $data['user_id'] = $record->users->nfsp_user_id;//用户号
                $data['name'] = isset($record->users->valid) ? $record->users->valid->name : '';//用户姓名1
                $data['phone_num'] = $record->users->phone_num;//手机号
                $data['num'] = isset($record->users->valid) ? $record->users->valid->num : '';//身份证号2
                $data['source_code'] = $record->users->source;//渠道号
                $data['source_name'] = '';//渠道来源
                $data['out_trade_id'] = $record->relationSummary->relationLoanApply->out_trade_id;//放款交易号
                $channel_name = $this->getChannelName($record->relationSummary->loan_request_id, $all_channel);
                $data['channel_name'] = $channel_name;//放款资方渠道
                $data['amount'] = $record->relationSummary->amount;//放款金额
                $data['period_count'] = $record->relationSummary->period_count;//分期期数
                $data['status'] = '逾期';//还款状态
                $data['period'] = $record->relationSummary->period;//当前期数
                $data['normal_date'] = $record->normal_date;//预计还款日期
                $data['failure'] = $record->money->failure;//失败原因
                $data['current_period'] = $record->period;//当前逾期是第几期
                $data['overdue_day'] = count_days(Carbon::now()->toDateString(), $record->normal_date);//逾期天数
                $data['repay_type'] = '';//还款类型
                $data['current_date'] = Carbon::now()->toDateString();//对账日期
                $commonData = array_merge($commonData, $data);
                $moneyInfo = RepaySummary::getRepayMoney($record);
                $isNew = RepaySummary::getLoanChannel($record->relationSummary->id);
                //资方
                $commonData['status_2'] = '';
                if ($record->money->status == RepayRecordMoney::STATUS_FOUR) {
                    if ($isNew) {//新数据资方已还款，在平台显示部分成功
                        $commonData['status_2'] = '部分成功';
                    }
                    $moneyData = [];
                } else {
                    $moneyData = array_merge($moneyData, $data);
                    $moneyData['all_money'] = $moneyInfo['amount'] + $moneyInfo['lending_money'] + $moneyInfo['money_fines_money'];//应收总金额
                    $moneyData['ys_amount'] = $moneyInfo['amount'];//应收本金
                    $moneyData['lending_money'] = $moneyInfo['lending_money'];//应收利息
                    $moneyData['money_fines_money'] = $moneyInfo['money_fines_money'];//资方罚息
                }
                //平台
                if($record->common->status == RepayRecordCommon::STATUS_FOUR) {
                    $commonData = [];
                } else {
                    $commonData['all_money'] = bcadd($moneyInfo['common_fines_money'], $moneyInfo['manage_money'], 2);
                    if (!$isNew) {
                        $commonData['ys_amount'] = $moneyInfo['amount'];//平台本金
                        $commonData['lending_money'] = $moneyInfo['lending_money'];//平台利息
                        $commonData['all_money'] = $commonData['all_money'] + $commonData['ys_amount'] + $commonData['lending_money'];
                    }
                    $commonData['common_fines_money'] = $moneyInfo['common_fines_money'];//平台罚息
                    $commonData['manage_money'] = $moneyInfo['manage_money'];//应收贷后管理费
                    $commonData['month_money'] = 0;//应收月保费
                }
                $excelDataMoney[] = $moneyData;
                $excelDataCommon[] = $commonData;
            }
        });
        $excelDataMoney = array_values(array_filter($excelDataMoney));
        $excelDataCommon = array_values(array_filter($excelDataCommon));
        $this->exportMoney($excelDataMoney, $to_mail);
        $this->exportCommon($excelDataCommon, $to_mail);
    }

    //导出资金数据
    protected function exportMoney($excelDataMoney, $to_mail)
    {
        $header_money = ['授信ID', '产品', '用户号', '用户姓名', '手机号', '身份证号', '渠道号', '渠道来源', '借款订单号', '放款资方渠道', '放款金额', '分期期数', '还款状态', '当前期数', '预计还款日期', '应收总金额', '应收本金', '应收利息', '资方罚息',
//            '平台罚息', '应收贷后管理费', '应收月保费',
            '失败原因', '当前逾期期数', '当前逾期天数', '还款类型', '导表日期'
        ];
        $fields_money = ['credit_id', 'product', 'user_id', 'name', 'phone_num', 'num', 'source_code', 'source_name', 'out_trade_id', 'channel_name', 'amount', 'period_count', 'status', 'period', 'normal_date', 'all_money', 'ys_amount', 'lending_money', 'money_fines_money',
//            'common_fines_money', 'manage_money', 'month_money',
            'failure', 'current_period', 'overdue_day', 'repay_type', 'current_date'];
        $spread = new Spreadsheet();
        $sheet = $spread->getActiveSheet();
        //设置header
        $len = count($header_money);
        for ($i = 0; $i < $len; $i++) {
            if ($i <= 25) {
                $sheet->setCellValue(chr(65 + $i) . "1", $header_money[$i]);
            } else {
                $n = floor($i / 26);
                $sheet->setCellValue(chr(65 + $n - 1) . chr(65 + ($i % 26)) . "1", $header_money[$i]);
            }
        }
        $len = count($fields_money);
        $i_num = [4, 6, 10, 11, 13, 15, 16, 17, 18, 20, 21, 24];
        foreach ($excelDataMoney as $key => $item) {
            $row = 2 + $key;
            for ($i = 0; $i < $len; $i++) {
                if ($i <= 25) {
                    $position = chr(65 + $i) . $row;
                } else {
                    $n = floor($i / 26);
                    $position = chr(65 + $n - 1) . chr(65 + ($i % 26)) . $row;
                }
                $sheet->setCellValueExplicit($position, $item[$fields_money[$i]], DataType::TYPE_STRING);
                if (in_array($i, $i_num)) {
                    $sheet->setCellValueExplicit($position, $item[$fields_money[$i]], DataType::TYPE_NUMERIC);
                }
            }
        }
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spread);
        $file = 'overdue' . date('Ymd') . '.xlsx';
        $filePath = storage_path($file);
        $writer->save($filePath);
        Mail::to($to_mail)->send(new OverdueMoneyMail($filePath));
        $this->info($file . ' export success!!');
//        unlink($filePath);
    }

    //导出平台数据
    protected function exportCommon($excelDataCommon, $to_mail)
    {
        $header_common = ['授信ID', '产品', '用户号', '用户姓名', '手机号', '身份证号', '渠道号', '渠道来源', '借款订单号', '放款资方渠道', '放款金额', '分期期数', '还款状态', '当前期数', '预计还款日期', '应收总金额',
            '平台罚息', '应收贷后管理费', '应收月保费', '应收本金', '应收利息', '失败原因', '当前逾期期数', '当前逾期天数', '还款类型', '导表日期',
            '是否还款成功'];
        $fields_common = ['credit_id', 'product', 'user_id', 'name', 'phone_num', 'num', 'source_code', 'source_name', 'out_trade_id', 'channel_name', 'amount', 'period_count', 'status', 'period', 'normal_date', 'all_money',
            'common_fines_money', 'manage_money', 'month_money', 'ys_amount', 'lending_money', 'failure', 'current_period', 'overdue_day', 'repay_type', 'current_date',
            'status_2'];
        $spread = new Spreadsheet();
        $sheet = $spread->getActiveSheet();
        //设置header
        $len = count($header_common);
        for ($i = 0; $i < $len; $i++) {
            if ($i <= 25) {
                $sheet->setCellValue(chr(65 + $i) . "1", $header_common[$i]);
            } else {
                $n = floor($i / 26);
                $sheet->setCellValue(chr(65 + $n - 1) . chr(65 + ($i % 26)) . "1", $header_common[$i]);
            }
        }
        $len = count($fields_common);
        $i_num = [4, 6, 10, 11, 13, 15, 16, 17, 18, 19, 20, 22, 23];
        foreach ($excelDataCommon as $key => $item) {
            $row = 2 + $key;
            for ($i = 0; $i < $len; $i++) {
                if ($i <= 25) {
                    $position = chr(65 + $i) . $row;
                } else {
                    $n = floor($i / 26);
                    $position = chr(65 + $n - 1) . chr(65 + ($i % 26)) . $row;
                }
                $sheet->setCellValueExplicit($position, $item[$fields_common[$i]], DataType::TYPE_STRING);
                if (in_array($i, $i_num)) {
                    $sheet->setCellValueExplicit($position, $item[$fields_common[$i]], DataType::TYPE_NUMERIC);
                }
            }
        }
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spread);
        $file = 'overdue' . date('Ymd') . '.xlsx';
        $filePath = storage_path($file);
        $writer->save($filePath);
        Mail::to($to_mail)->send(new OverdueCommonMail($filePath));
        $this->info($file . ' export success!!');
        unlink($filePath);
    }
    //全量客户数及客户贷款余额
    public  function  normalExce(){
        $data = $this->normalOverdueData();
        $excelData []=$data;
        $header = ['客户数', '贷款余额'];
        $fields = ['count', 'total'];
        $spread = new Spreadsheet();
        $sheet = $spread->getActiveSheet();
        //设置header
        $len = count($header);
        for ($i = 0; $i < $len; $i++) {
            if ($i <= 25) {
                $sheet->setCellValue(chr(65 + $i) . "1", $header[$i]);
            } else {
                $n = floor($i / 26);
                $sheet->setCellValue(chr(65 + $n - 1) . chr(65 + ($i % 26)) . "1", $header[$i]);
            }
        }
        $len = count($fields);
        $i_num = [2, 2];
        foreach ($excelData as $key => $item) {
            $row = 2 + $key;
            for ($i = 0; $i < $len; $i++) {
                if ($i <= 25) {
                    $position = chr(65 + $i) . $row;
                } else {
                    $n = floor($i / 26);
                    $position = chr(65 + $n - 1) . chr(65 + ($i % 26)) . $row;
                }
                $sheet->setCellValueExplicit($position, $item[$fields[$i]], DataType::TYPE_STRING);
                if (in_array($i, $i_num)) {
                    $sheet->setCellValueExplicit($position, $item[$fields[$i]], DataType::TYPE_NUMERIC);
                }
            }
        }
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spread);
        $file = 'normal' . date('Ymd') . '.xlsx';
        $filePath = storage_path($file);
        $msg = date('Y-m-01');
        $writer->save($filePath);
        Mail::to($this->overdueMail)->send(new normaldueMoreMail($filePath,$msg));
        $this->info($file . ' export success!!');
    }

    public function normalOverdueData()
    {
        $firstDay = date('Y-m-01');
        $this->info('start');
        $t0 = microtime(true);
        $total = 0;//人数
        $amount = 0;//贷款余额
        $temp_amount = 0; //未还剩余本金
        $temp_lending = 0; //未还剩余利息
        $temp_Service  = 0;//未还服务费

        RepayRecord::distinct()
            ->leftJoin('repay_summaries as e','e.id','=','repay_records.summary_id')
            ->leftJoin('repay_record_months as b', 'repay_records.id', '=', 'b.record_id')
            ->leftJoin('repay_record_moneys as c', 'repay_records.id', '=', 'c.record_id')
            ->leftJoin('repay_record_commons as d', 'repay_records.id', '=', 'd.record_id')
            ->where('pay_off', RepayRecord::PAY_OFF_ONE)
            ->where('e.period', '<>', 0)
            ->where('e.created_at', '<', $firstDay)
            ->orderBy('repay_records.uid')
            ->select(
                'e.uid',
                //a 是repay_records
                'repay_records.amount',
                'repay_records.repay_date as repay_date',
                //b 是repay_record_months
                'b.amount as month_amount',
                //c 是repay_record_moneys
                'c.lending_money as money_lending',
                'c.manage_money as money_manage',
                //d 是repay_record_commons
                'd.lending_money as common_lending', //兼容老数据
                'd.manage_money as common_manage')
            ->chunk(2000, function (Collection $summary_collection) use (&$total,&$amount,&$temp_amount,&$temp_lending,&$temp_Service, $t0) {
                $summaries_exchange = ArrayTools::valuesToKey($summary_collection->toArray(), 'uid');
                foreach ($summaries_exchange as $key=>$info){
                    if(empty($info)){
                        continue;
                    }
                    $isnormal = $this->isNormalUser($key);
                    if($isnormal){ //正常用户
                        $total = $total+1;
                        foreach ($info as $k =>$v){
                            if(empty($v['repay_date'])){
                                $temp_amount += $v['amount'];
                                $temp_lending += ($v['money_lending'] + $v['common_lending'])/100;
                                $temp_Service += ($v['money_manage'] + $v['common_manage'])/100;
                            }
                        }
                        // $pertotal = ($temp_amount +$temp_lending  +$temp_Service);
                    }else{
                        continue;
                    }
                }
                $amount = ($temp_amount +$temp_lending  +$temp_Service);
                $this->info('人数-余额: '.$total.'_'.$amount.' ;批处理时间:' . round(microtime(true) - $t0, 2) . 's');

            });
        $time = round(microtime(true) - $t0,2);
        $this->info('人数-余额: '.$total.'_'.$amount.' ;done, 执行时间' . $time . 's');
        return array('count'=>$total,'total'=>$amount);
    }
    public  function  isNormalUser($userId){
        $curent_period = RepaySummary::where('uid',$userId)->value('period');
        if($curent_period > 1){
            $where = ['uid'=>$userId,'period'=>$curent_period-1];
        }else {
            $where = ['uid' => $userId, 'period' => $curent_period];
        }
        $info = RepayRecord::where($where)->select('normal_date','repay_date')->first();
        if($curent_period > 1){
            if($info->repay_date){
                if($info->repay_date <= $info->normal_date){
                    return true;
                }else{
                    return false;
                }
            }else{
                return false;
            }
        }else{
            if($info->repay_date){
                if($info->repay_date <= $info->normal_date){
                    return true;
                }else{
                    return false;
                }
            }elseif(empty($info->repay_date) && $info->normal_date >= date('Y-m-d')){
                return true;
            }else{
                return false;
            }
        }

    }
    //每日的T预计还款表
    public function huankuanT()
    {
        $time = $this->option('time');
        $date = date('Y-m-d');
        if(!empty($time)){
            $date = $time;
        }
        $this->huankuanData($date);
    }
    //T2预计还款表
    public function huankuanT2()
    {
        $time = $this->option('time');
        $date = date('Y-m-d',strtotime('+2 day'));
        if(!empty($time)){
            $date = $time;
        }
        $this->huankuanData($date);
    }
    public  function huankuanData($date){
        $info =   RepayRecord::leftJoin('repay_summaries as rs','rs.id','=','repay_records.summary_id')
            ->leftJoin('users as u','u.id','=','repay_records.uid')
            ->leftJoin('user_cert_ocrs as uco','uco.user_id','=','repay_records.uid')
            ->leftJoin('user_bank_cards as ub','ub.id','=','repay_records.bank_cards_id')
            ->where('repay_records.normal_date', '=', $date)
            ->where('repay_records.status','<>', RepayRecord::STATUS_FOUR)
            ->where('repay_records.pay_off', RepayRecord::PAY_OFF_ONE)
            ->select([
                'repay_records.normal_date',
                'repay_records.uid',
                'repay_records.id as record_id',
                //repay_summaries
                'rs.loan_request_id',
                //users
                'u.phone_num',
                //user_sync_infos
                'uco.name',
                //user_bank_cards
                'ub.bank_name_show',
                'ub.bank_name',
                'ub.card_num',

            ])
            ->groupBy('repay_records.id')
            ->get();
        $excelData = array();
        $all_channel = SettingChannel::pluck('channel_name', 'channel_code')->toArray();
        if(!empty($info)){
            foreach ($info as $k => $val){
                $isnormal = $this->isNormalUser($val->uid);
                if($isnormal) { //正常用户
                    $arrayData = array();
                    $arrayData['uid'] = $val->uid;
                    $arrayData['name'] = $val->name;
                    $arrayData['phone_num'] = $val->phone_num;
                    $arrayData['normal_date'] = $val->normal_date;
                    $remainInfo = $this->getAmoutCurrent($val->record_id);//还款金额
                    $arrayData['amout'] = $remainInfo['all_money'];
                    $arrayData['card_num'] = $val->card_num;
                    $arrayData['bank_name_show'] = $val->bank_name_show ? $val->bank_name_show : ($val->bank_name ? $val->bank_name : '');
                    $channel_name = $this->getChannelName($val->loan_request_id, $all_channel);
                    $arrayData['channel_name'] = $channel_name;
                    $excelData[] = $arrayData;
                }
            }
        }
        $this->exportHuankuan($excelData,$date);


    }
    //获取当期的各种金额
    public  function  getAmoutCurrent($record_id){
        $records = RepayRecord::with('money:lending_money,manage_money,record_id', 'common:lending_money,manage_money,record_id')->where('id', $record_id)->where('status', '<>', RepayRecord::STATUS_FOUR)->select('id', 'amount')->first();
        $all_money = $remainAmount = $remainLending = $remainManage = 0;
        if($records){
            $remainAmount = $records->amount;
            $remainMoneyLending = $records->money->lending_money;
            $remainCommonLending = $records->common->lending_money;
            $remainLending = bcadd($remainMoneyLending, $remainCommonLending, 2);
            $remainMoneyManage = $records->money->manage_money;
            $remainCommonManage = $records->common->manage_money;
            $remainManage = bcadd($remainMoneyManage, $remainCommonManage, 2);
            $all_money = bcadd(bcadd($remainAmount, $remainLending, 2),$remainManage,2);
        }
        return ['all_money'=>$all_money,'remainAmount'=>$remainAmount, 'remainLending'=>$remainLending, 'remainManage'=>$remainManage];

    }
//导出平台数据
    protected function exportHuankuan($excelDataCommon,$date)
    {
        $header_common = ['用户姓名', '手机号', '计划还款时间', '计划还款金额(元)', '储蓄卡卡号', '储蓄卡银行名称', '放款渠道'];
        $fields_common = ['name', 'phone_num', 'normal_date', 'amout', 'card_num', 'bank_name_show', 'channel_name'];
        $spread = new Spreadsheet();
        $sheet = $spread->getActiveSheet();
        //设置header
        $len = count($header_common);
        for ($i = 0; $i < $len; $i++) {
            if ($i <= 25) {
                $sheet->setCellValue(chr(65 + $i) . "1", $header_common[$i]);
            } else {
                $n = floor($i / 26);
                $sheet->setCellValue(chr(65 + $n - 1) . chr(65 + ($i % 26)) . "1", $header_common[$i]);
            }
        }
        $len = count($fields_common);
        $i_num = [3];
        foreach ($excelDataCommon as $key => $item) {
            $row = 2 + $key;
            for ($i = 0; $i < $len; $i++) {
                if ($i <= 25) {
                    $position = chr(65 + $i) . $row;
                } else {
                    $n = floor($i / 26);
                    $position = chr(65 + $n - 1) . chr(65 + ($i % 26)) . $row;
                }
                $sheet->setCellValueExplicit($position, $item[$fields_common[$i]], DataType::TYPE_STRING);
                if (in_array($i, $i_num)) {
                    $sheet->setCellValueExplicit($position, $item[$fields_common[$i]], DataType::TYPE_NUMERIC);
                }
            }
        }
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spread);
        $file = 'remain_users' . date('Ymd',strtotime($date)) . '.xlsx';
        $filePath = storage_path($file);
        $writer->save($filePath);
        Mail::to($this->remain_mail)->send(new RemainMail($filePath,$date));
        $this->info($file . ' export success!!');
        unlink($filePath);
    }
    //还款监控
    public function  repaymentMonitor(){
        //查询出当天的所有还款数据和应还总金额
        $date = date('Y-m-d');
        $records = RepayRecord::with('money:lending_money,manage_money,record_id', 'common:lending_money,manage_money,record_id')
            ->where('repay_records.normal_date',$date)
            ->select('id','uid','normal_date','amount','updated_at','status')
            ->get()->toArray();
        $count  = $all_money = 0;
        $excelData = [];
        if($records){
            $count = count($records);
            $ben = array_sum(array_column($records,'amount'));
            $remainMoneyLending = array_sum(array_column(array_column($records,'money'),'lending_money'));
            $remainCommonLending = array_sum(array_column(array_column($records,'common'),'lending_money'));
            $remainLending = bcadd($remainMoneyLending, $remainCommonLending, 2);
            $remainMoneyManage = array_sum(array_column(array_column($records, 'money'), 'manage_money'));
            $remainCommonManage = array_sum(array_column(array_column($records, 'common'), 'manage_money'));
            $remainManage = bcadd($remainMoneyManage, $remainCommonManage, 2);
            $all_money = $ben + $remainLending + $remainManage;
        }

        //查询出当天的已经还款的数据
//        $repayList = RepayRecord::with('money:lending_money,manage_money,record_id', 'common:lending_money,manage_money,record_id')
//            ->where(['repay_records.normal_date'=>$date,'repay_records.repay_date'=>$date,'repay_records.status'=>4])
//            ->select('id','uid','normal_date','amount','repay_date','updated_at','status')
//            ->orderBy('updated_at')
//            ->get()->toArray();
        $repayList = RepayRecord::with('money:lending_money,manage_money,record_id', 'common:lending_money,manage_money,record_id')
            ->where(['repay_records.normal_date'=>$date,'repay_records.status'=>4])
            ->select('id','uid','normal_date','amount','repay_date','updated_at','status','pay_off')
            ->orderBy('updated_at')
            ->get()->toArray();
        if(!empty($repayList)){
            $arr =$arr2= $arr3 =[];
            foreach ($repayList as $k =>$val){
                if($val['repay_date'] == $date){
                    $paytime = strtotime($val['updated_at']);
                    if($paytime >=  strtotime(date('Y-m-d 0:00:01')) && $paytime <= strtotime(date('Y-m-d 2:00:00'))){
                        $arr[0][] =$val;
                    }else if($paytime >= strtotime(date('Y-m-d 2:00:01')) && $paytime <= strtotime(date('Y-m-d 4:00:00'))){
                        $arr[1][] =$val;
                    }else if($paytime >= strtotime(date('Y-m-d 4:00:01')) && $paytime <= strtotime(date('Y-m-d 6:00:00'))){
                        $arr[2][] =$val;
                    }else if($paytime >= strtotime(date('Y-m-d 6:00:01')) && $paytime <= strtotime(date('Y-m-d 8:00:00'))){
                        $arr[3][] =$val;
                    }else if($paytime >= strtotime(date('Y-m-d 8:00:01')) && $paytime <= strtotime(date('Y-m-d 10:00:00'))){
                        $arr[4][] =$val;
                    }else if($paytime >= strtotime(date('Y-m-d 10:00:01')) && $paytime <= strtotime(date('Y-m-d 12:00:00'))){
                        $arr[5][] =$val;
                    }else if($paytime >= strtotime(date('Y-m-d 12:00:01')) && $paytime <= strtotime(date('Y-m-d 14:00:00'))){
                        $arr[6][] =$val;
                    }else if($paytime >= strtotime(date('Y-m-d 14:00:01')) && $paytime <= strtotime(date('Y-m-d 16:00:00'))){
                        $arr[7][] =$val;
                    }else if($paytime >= strtotime(date('Y-m-d 16:00:01')) && $paytime <= strtotime(date('Y-m-d 18:00:00'))){
                        $arr[8][] =$val;
                    }else if($paytime >= strtotime(date('Y-m-d 18:00:01')) && $paytime <= strtotime(date('Y-m-d 20:00:00'))){
                        $arr[9][] =$val;
                    }else if($paytime >= strtotime(date('Y-m-d 20:00:01')) && $paytime <= strtotime(date('Y-m-d 22:00:00'))){
                        $arr[10][] =$val;
                    }else if($paytime >= strtotime(date('Y-m-d 22:00:01')) && $paytime <= strtotime(date('Y-m-d 24:00:00'))){
                        $arr[11][] =$val;
                    }
                }elseif ($val['repay_date'] < $date){
                    if($val['pay_off'] == 1){//1:分期还款,2:全部还清',
                        $arr2[] = $val;

                    }elseif ($val['pay_off'] == 2){//1:分期还款,2:全部还清',
                        $arr3[] = $val;
                    }
                }
            }
            //统计提前结清和提前还款
            $ti_count  = $ti_all_money = 0;
            $ti_count = count($arr2);
            $ti_ben = array_sum(array_column($arr2,'amount'));
            $ti_MoneyLending = array_sum(array_column(array_column($arr2,'money'),'lending_money'));
            $ti_CommonLending = array_sum(array_column(array_column($arr2,'common'),'lending_money'));
            $ti_Lending = bcadd($ti_MoneyLending, $ti_CommonLending, 2);
            $ti_MoneyManage = array_sum(array_column(array_column($arr2, 'money'), 'manage_money'));
            $ti_CommonManage = array_sum(array_column(array_column($arr2, 'common'), 'manage_money'));
            $ti_Manage = bcadd($ti_MoneyManage, $ti_CommonManage, 2);
            $ti_all_money = $ti_ben + $ti_Lending + $ti_Manage;

            $jie_count  = $jie_all_money = 0;
            $jie_count = count($arr3);
            $jie_ben = array_sum(array_column($arr3,'amount'));
            $jie_MoneyLending = array_sum(array_column(array_column($arr3,'money'),'lending_money'));
            $jie_CommonLending = array_sum(array_column(array_column($arr3,'common'),'lending_money'));
            $jie_Lending = bcadd($jie_MoneyLending, $jie_CommonLending, 2);
            $jie_MoneyManage = array_sum(array_column(array_column($arr3, 'money'), 'manage_money'));
            $jie_CommonManage = array_sum(array_column(array_column($arr3, 'common'), 'manage_money'));
            $jie_Manage = bcadd($jie_MoneyManage, $jie_CommonManage, 2);
            $jie_all_money = $jie_ben + $jie_Lending + $jie_Manage;
            for ($i=0;$i<12;$i++){
                $repay_total = $repay_all_money = $repay_ben = $repay_Lending =$repay_Manage =0;
                $inchour = ($i+1)*2;
                $end_time = date('Y-m-d H:i:s',strtotime($inchour.' hours',strtotime(date('Y-m-d 0:00:0'))));
                $excelData[$i] =  [
                    'date' => $date,
                    'total' => $count,
                    'all_money' =>$all_money,
                    'start_time' => date('Y-m-d 0:00:01'),
                    'end_time' => $end_time,
                    'jie_count' => $jie_count,
                    'jie_all_money' => $jie_all_money,
                    'ti_count' => $ti_count,
                    'ti_all_money' => $ti_all_money,
                ];
                if(isset($arr[$i])){
                    $repay_ben = array_sum(array_column($arr[$i],'amount'));
                    $repay_MoneyLending = array_sum(array_column(array_column($arr[$i],'money'),'lending_money'));
                    $repay_CommonLending = array_sum(array_column(array_column($arr[$i],'common'),'lending_money'));
                    $repay_Lending = bcadd($repay_MoneyLending, $repay_CommonLending, 2);
                    $repay_MoneyManage = array_sum(array_column(array_column($arr[$i], 'money'), 'manage_money'));
                    $repay_CommonManage = array_sum(array_column(array_column($arr[$i], 'common'), 'manage_money'));
                    $repay_Manage = bcadd($repay_MoneyManage, $repay_CommonManage, 2);
                    $repay_all_money = ($repay_ben + $repay_Lending + $repay_Manage);
                    $repay_total = count($arr[$i]);
                    if($i >=1){
                        $excelData[$i]['repay_total'] = $repay_total + $excelData[$i-1]['repay_total'];
                        $excelData[$i]['repay_all_money'] = $repay_all_money + $excelData[$i-1]['repay_all_money'];
                    }else{
                        $excelData[$i]['repay_total'] = $repay_total;
                        $excelData[$i]['repay_all_money'] = $repay_all_money;
                    }
                    $excelData[$i]['percent'] = round((($excelData[$i]['repay_all_money']+$ti_all_money+$jie_all_money)/$all_money)*100,2).'%';
                    $excelData[$i]['repay_inctotal'] = $repay_total;
                    $excelData[$i]['repay_incall_money'] = $repay_all_money;

                }else{
                    $excelData[$i]['repay_total'] = isset($excelData[$i-1]['repay_total'])?$excelData[$i-1]['repay_total']:0;
                    $excelData[$i]['repay_all_money'] = isset($excelData[$i-1]['repay_all_money'])?$excelData[$i-1]['repay_all_money']:0;
                    $excelData[$i]['percent']= isset($excelData[$i-1]['percent'])?$excelData[$i-1]['percent']:'0%';
                    $excelData[$i]['repay_inctotal'] = 0;
                    $excelData[$i]['repay_incall_money'] = 0;
                }
            }
            $this->exportMonitor($excelData,$date);

        }

    }
    public  function  exportMonitor($excelData,$date){
        $header_common = ['日期', '应还笔数', '应还金额','提前结清笔数','提前结清金额','提前还款笔数','提前还款金额', '时间起始', '时间结束', '已还笔数', '已还金额','金额比率','分时笔数','分时金额'];
        $fields_common = ['date', 'total', 'all_money','jie_count','jie_all_money','ti_count','ti_all_money', 'start_time', 'end_time', 'repay_total', 'repay_all_money','percent','repay_inctotal','repay_incall_money'];
        $spread = new Spreadsheet();
        $sheet = $spread->getActiveSheet();
        //设置header
        $len = count($header_common);
        for ($i = 0; $i < $len; $i++) {
            if ($i <= 25) {
                $sheet->setCellValue(chr(65 + $i) . "1", $header_common[$i]);
            } else {
                $n = floor($i / 26);
                $sheet->setCellValue(chr(65 + $n - 1) . chr(65 + ($i % 26)) . "1", $header_common[$i]);
            }
        }
        $len = count($fields_common);
        $i_num = [1,2,3,4,5,6,9,10,12,13];
        foreach ($excelData as $key => $item) {
            $row = 2 + $key;
            for ($i = 0; $i < $len; $i++) {
                if ($i <= 25) {
                    $position = chr(65 + $i) . $row;
                } else {
                    $n = floor($i / 26);
                    $position = chr(65 + $n - 1) . chr(65 + ($i % 26)) . $row;
                }
                $sheet->setCellValueExplicit($position, $item[$fields_common[$i]], DataType::TYPE_STRING);
                if (in_array($i, $i_num)) {
                    $sheet->setCellValueExplicit($position, $item[$fields_common[$i]], DataType::TYPE_NUMERIC);
                }
            }
        }
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spread);
        $file = 'repay_Monitor' . date('Ymd',strtotime($date)) . '.xlsx';
        $filePath = storage_path($file);
        $writer->save($filePath);
        Mail::to($this->repay_Monitor)->send(new RepayMonitorMail($filePath,$date));
        $this->info($file . ' export success!!');
        unlink($filePath);
    }

}
